| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Query tuning help
Just in case anyone out there is interested, we use the term "Mickey
Mouse schema" to refer to a very specific design tactic. We're *not*
using the term's slang meaning of "unimportant" or "uninspired."
(...Which always seemed odd to me, because MM is a really strong, high
quality brand.)
Specifically, when we use the term "Mickey Mouse schema" when discussing SQL optimization (and we probably shouldn't), we mean that the subschema being queried looks something like this:
TABLE -- FILTER
/
TABLE
\
TABLE -- FILTER
If you draw circles around the entities and rotate this drawing 90 degrees to the left, you'll see that it kinda looks like the shape of Mickey Mouse's head.
It's really difficult to optimize queries that look this way, because you have to do potentially very inefficient joins before you can apply all the filter conditions.
Something else you might be able to consider (I know, your list is long enough as it is) is the new bitmap join index. I think this is supposed to put join information in a single index out "in the ears" where it might save a lot of workload. But watch out: anytime you see the word "bitmap," it means you'd better not be doing much DML on the participating segments.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on Oracle=AE System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of DENNIS
WILLIAMS
Sent: Tuesday, September 10, 2002 4:17 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: SQL Query tuning help
Thanks everyone for your wonderful suggestions. And thanks for leaving
the
"hey stupid" off your reply header :-)
Rachel - Thanks for the bitmapped idea. These tables don't change often,
so
that may be a good alternative.
Iain - Thanks so much for the detailed suggestions.
Rick - Good sanity check, yes, I analyzed the tables.
Jared - RET has 281 values, pretty evenly distributed
Cary - Query returns 185 rows.
Bill - Thanks for the suggestions and insights.
Stephane - Good notice that only am values are used. Guess that is why Oracle accessed the data blocks anyway with my new indexes. Duh. Good ideas.
Jeff - Thanks for the "Mickey Mouse" tag. I may need that in the future. Previously this data was on an old mainframe and the business itself was restricted by the inflexibility. My gut reaction was that they overcompensated.=20
Thanks everyone for the wonderful ideas. I was just given a hot project,
so
it may be a day or two before I get a chance to explore all of them, but
I'll let you know.
=A0
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>=20
=A0
-----Original Message-----
Sent: Tuesday, September 10, 2002 2:19 PM
To: Multiple recipients of list ORACLE-L
I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
optimizations, but
so far have made no improvements. I would appreciate any suggestions.
SELECT am.lid, am.name
FROM am, so, sa
WHERE so.lid =3D am.lid
AND so.key_ =3D sa.so_key AND am.active =3D 1 AND so.code =3D 11 AND sa.ret =3D 'SB'
Tables:
am - 250,000 rows, 220,000 rows have active =3D 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique,
code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique.
Now, you'll probably say there is essentially a 1-1 relationship between
so
and sa. You are right, but the developer insists this flexibility is
essential.
The query executes in 16 seconds and returns 185 rows. This is felt to
be
too slow for an online lookup screen.
explain plan results:
SELECT STATEMENT Cost =3D 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM
Here is what I've tried so far:
Using hints to force Oracle to use indexes.
Query Plan
HASH JOIN
TABLE ACCESS BY INDEX ROWID SA
INDEX FULL SCAN SO_KEY3
TABLE ACCESS BY INDEX ROWID SO
INDEX RANGE SCAN PRG_CODE3
TABLE ACCESS BY INDEX ROWID AM
INDEX UNIQUE SCAN LID6 =20
Timing result 25 minutes
Next I tried creating new indexes that combine both the accessing column
as
well as the retrieved column, thinking that Oracle could get the result
from
the index block and not need to retrieve the data block.=20
create index test1 on am (lid, active);
create index test2 on sa (so_key, code);
SELECT STATEMENT Cost =3D 2951
SORT AGGREGATE
HASH JOIN
HASH JOIN
INDEX FULL SCAN TEST2
TABLE ACCESS FULL SO
TABLE ACCESS BY INDEX ROWID AM
INDEX RANGE SCAN TEST1
=20
=20
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--=20
Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --=20
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in Received on Tue Sep 10 2002 - 15:57:58 CDT
 |
 |