Re: Query optimization
Date: Fri, 10 Jan 2014 15:39:54 -0800 (PST)
Message-ID: <4093f5d1-6d9f-4fb4-b9d5-cb223895daa1_at_googlegroups.com>
On Friday, January 10, 2014 10:03:50 AM UTC-7, Mark wrote:
> On Fri, 10 Jan 2014 08:24:45 -0800 (PST), ddf <oratune_at_msn.com> wrote:
>
>
>
> >On Friday, January 10, 2014 6:14:23 AM UTC-7, Mark wrote:
>
> >> I have a question about index usage in table queries. We need to do
>
> >>
>
> >> fast queries on new tables. Therefore the standard method of
>
> >>
>
> >> generating table statistics does not work. We have verified that
>
> >>
>
> >> Oracle is not using the indexes.
>
> >>
>
> >>
>
> >>
>
> >> Assuming that the tables are less than 1 day old and have 1-2Mio
>
> >>
>
> >> records/hour written to them what is the best way to achieve this?
>
> >>
>
> >>
>
> >>
>
> >> An added complication is that the tables are referred to by synonyms
>
> >>
>
> >> as the user has no way to predict the actual table name.
>
> >>
>
> --snip--
>
> >
>
> >If Oracle determines that more than 30% of the table data will be returned then the cost of an index access path is far greater than the cost for a table scan which is why the second run used full table scans rather than the indexes. Note that when statistics were NOT gathered dynamic sampling caused Oracle to use the indexes. This example was run using 11.2.0.3; you may be running an older release (a fact you did not present in your original post) so please report which release of Oracle you are using.
>
>
>
> All the selects should only return one row.
>
>
>
> Version is 11.1.0.7.0.
>
>
>
> >Also, please report how you determined that the indexes are not being used; you posted no execution plans to prove your assertion.
>
>
>
> I was told this by the DBA. I don't know how to do this with Oracle. I
>
> usually work with Ingres.
>
>
>
> --
>
> (\__/) M.
>
> (='.'=) If a man stands in a forest and no woman is around
>
> (")_(") is he still wrong?
Notice the 'set autotrace on' statement in my previous example; that will give you the plan plus the query execution statistics. I have modified my example to use both a single-column and a multi-column index and to return only one row from the 1.8 million rows in the table:
SQL> SQL> -- SQL> -- Create an empty table with no indexes SQL> -- SQL> create table emp2 as select * from emp where 0=1;
Table created.
SQL> SQL> -- SQL> -- Create an index on the empty table SQL> -- SQL> create index empno_idx on emp2(empno);
Index created.
SQL> SQL> -- SQL> -- Populate the table (will insert 1,800,000 rows) SQL> -- SQL> SQL> begin 2 for i in 1..100000 loop 3 insert into emp2 4 select * from emp; 5 6 update emp2 7 set empno = empno+i, mgr=mgr+i, updated='YES' 8 where updated is null; 9 10 commit; 11 end loop; 12 13 commit;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> SQL> -- SQL> -- Select from emp2 and see if any indexes are used SQL> -- SQL> set autotrace on SQL> SQL> -- select * SQL> -- from emp2 SQL> -- where job = 'CLERK'; SQL> SQL> -- select * SQL> -- from emp2 SQL> -- where nvl(mgr,0000)=0000; SQL> SQL> select *
2 from emp2
3 where empno = (select min(empno)+1 from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO UPD ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
7370 SMITH CLERK 7903 17-DEC-80 800 20 YES
Execution Plan
Plan hash value: 1300931333
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 90 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 1 | 90 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMPNO_IDX | 82203 | | 1 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | TABLE ACCESS FULL | EMP | 18 | 234 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))
Note
- dynamic sampling used for this statement (level=2)
Statistics
7 recursive calls
1 db block gets
859 consistent gets
0 physical reads
0 redo size
1094 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Generate statistics
SQL> --
SQL> exec dbms_stats.gather_table_stats('GRIBNAUT', 'EMP2', estimate_percent=>100, cascade=>true)
PL/SQL procedure successfully completed.
SQL> SQL> -- SQL> -- Try the queries again SQL> -- SQL> set autotrace on SQL> SQL> -- select * SQL> -- from emp2 SQL> -- where job = 'CLERK'; SQL> SQL> -- select * SQL> -- from emp2 SQL> -- where nvl(mgr,0000)=0000; SQL> SQL> select *
2 from emp2
3 where empno = (select min(empno)+1 from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO UPD ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
7370 SMITH CLERK 7903 17-DEC-80 800 20 YES
Execution Plan
Plan hash value: 1300931333
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18 | 738 | 22 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 18 | 738 | 19 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMPNO_IDX | 18 | | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | TABLE ACCESS FULL | EMP | 18 | 234 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))
Note
- dynamic sampling used for this statement (level=2)
Statistics
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1094 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Drop the empno index and replace it
SQL> --
SQL> drop index empno_idx;
Index dropped.
SQL> create index ename_empno_idx on emp2(ename, empno);
Index created.
SQL> SQL> -- SQL> -- Try the queries again SQL> -- SQL> set autotrace on SQL> SQL> -- select * SQL> -- from emp2 SQL> -- where job = 'CLERK'; SQL> SQL> -- select * SQL> -- from emp2 SQL> -- where nvl(mgr,0000)=0000; SQL> SQL> select *
2 from emp2
3 where empno = (select min(empno)+1 from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO UPD ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---
7370 SMITH CLERK 7903 17-DEC-80 800 20 YES
Execution Plan
Plan hash value: 2131058526
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18 | 738 | 26 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 18 | 738 | 23 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | ENAME_EMPNO_IDX | 18 | | 20 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 13 | | | | 4 | TABLE ACCESS FULL | EMP | 18 | 234 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))
filter("EMPNO"= (SELECT MIN("EMPNO")+1 FROM "EMP" "EMP"))
Note
- dynamic sampling used for this statement (level=2)
Statistics
4 recursive calls
0 db block gets
52 consistent gets
32 physical reads
0 redo size
1094 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
11.2.0.x provides the INDEX SKIP SCAN step, and I'm not certain that 11.1.0.x does. If it doesn't then having a concatenated index (like the last index created in the example above) might cause Oracle to perform a full table scan since the column in the where clause isn't the leading column of the index.
David Fitzjarrell Received on Sat Jan 11 2014 - 00:39:54 CET