Re: can't get join on two large tables to use_nl or indexes

On May 31, 1:27 pm, grasp06110 <grasp06..._at_yahoo.com> wrote:
> > Woo-hoo, got it to work,
>
> > If I reverse the columns in the index I get the performance I'm
> > looking for. In other words the behavior I am observing is:
>
> > If the column with the handful of distinct values is used first in the
> > index the index is ignored in spite of several attemts to get it
> > recognized.
>
> > If the column with the number of distinct values of about 1/10 the
> > number of rows in the table is used first in the index then the index
> > is used if I do something like the following:
>
> > select
> > /*+ index(ccp ccp_sid_pid)*/
> > *
> > from
> > str,
> > ccp
> > where
> > str.sid = ccp.sid
> > and property = 'FOO'
>
> > SELECT STATEMENT Optimizer=CHOOSE (Cost=3941922 Card=953924
> > Bytes=915767040)
> > NESTED LOOPS (Cost=3941922 Card=953924 Bytes=915767040)
> > TABLE ACCESS (FULL) OF STR (Cost=60153 Card=1293923
> > Bytes=1212405851)
> > TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCP (Cost=3 Card=1
> > Bytes=23)
> > INDEX (UNIQUE SCAN) OF CCP_SID_PID (UNIQUE) (Cost=2 Card=1)
>
> > This query returns data in about 30 msec v several minutes!
>
> > I am supprized at the cost (3941922) given that I get first rows so
> > quickly.
>
> > If I do a select count(*) from the above query and add RULE to the
> > hint I get back the count (about 1million) in about 15 seconds (I'm
> > guessing that this is a good approximation of how long it will take
> > Oracle to access all of the rows?). It takes about 1 sec to get back
> > the first 10k rows. Fortunately, most of the people looking at these
> > data will only be interested in the first rows (generally < the first
> > 10k rows.
>
> One more minor detail: the rule uses the alias of the table.
>
> This ignores the index:
>
> select
> /*+ index(my_table my_index) */
> *
> from
> my_table mt,
> my_other_table mot
> where
> mt.motid = mot.motid
> and some_col = 'FOO'
>
> This uses the index:
>
> select
> /*+ index(mt my_index) */
> *
> from
> my_table mt,
> my_other_table mot
> where
> mt.motid = mot.motid
> and some_col = 'FOO'
>
> Can someone recommend a good book(s) or other resouce(s) that provides
> (preferably) comprehensive information on Oracle query (and database)
> tuning as well as act as a refernce for quick info regarding specific
> questions for Oracle 10g as well as older implementations
> (specifically 9i)?
>
> Thanks,
> John- Hide quoted text -
>
> - Show quoted text -

The Performance Tuning manual to start, then Jonathan Lewis' CBO book (and blog) for true understanding, and Cary Milsap's book for a decent methodology. Also see http://www.dbaoracle.net/readme-cdos.htm#subj10 and google for people like Jonathan Gennick, Wolfgang Breitling, Tom Kyte (asktom.oracle.com and his books, too), Tim Gorman, indeed, anyone on http://www.oaktable.net/members.jsp (with various specialties). There are plenty of "how to read a trace file" papers floating around.

Iterations of reading and rereading the books and getting hands dirty seems to work best for many people.

jg

--
@home.com is bogus.  "Mozart, in a letter to his father, reported that
at the première of his "Paris" symphony, the audience actually
applauded in the middle of the last movement. Was young Wolfgang
offended? Not in the least! He wrote that he was so happy he recited a
rosary on the spot!"  - Max R.