-
Hi
I have a small SQL, was wondering if anyone´s got any suggestion to improve it
cheers
SELECT A.*, B.OFICINAS, C.FOTOS
FROM
ICDBA.IC_PROMOCIONES A,
(select count(COD_OFI) AS OFICINAS, COD_AGE, COD_RED, COD_PRM
FROM ICDBA.PR_PROMOCIONOFI
GROUP BY COD_AGE, COD_RED, COD_PRM) B,
(SELECT COUNT(COD_TIP_IMG) as fotos, COD_RED, COD_AGE, COD_PRM
FROM ICDBA.IC_FICH_GRAFICOS
GROUP BY COD_AGE, COD_RED, COD_PRM) C
WHERE
A.COD_AGE = B.COD_AGE AND
A.COD_RED = B.COD_RED AND
A.COD_PRM = B.COD_PRM AND
c.COD_AGE (+)= a.COD_AGE AND
c.COD_RED (+)= a.COD_RED AND
c.COD_PRM (+)= a.COD_PRM
AND A.COD_RED=2
AND A.COD_AGE=596
AND A.COD_PRM=3331
/
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Cost= 5
NESTED LOOPS OUTER
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID IC_PROMOCIONES
INDEX RANGE SCAN IC_PROMOCIONES_PK
VIEW
SORT GROUP BY
INDEX RANGE SCAN PR_PROMOCIONOFI_PK
VIEW
SORT GROUP BY
TABLE ACCESS FULL IC_FICH_GRAFICOS
-
Phew! What a query.
By the way what is table size of each table in the query and how much time is this particular query taking.
-
Well the tables arent big since this is from our development database, there are only a couple of hundreds rows in each table
table ic_promociones is the parent table and the other two are the child table so those two would be pretty big once we go to production.
The timing is 00:00:00:70
Excution plan using autotrace on (shows more details)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=445)
1 0 NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=445)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=393)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
t=1 Card=1 Bytes=341)
4 3 INDEX (RANGE SCAN) OF 'IC_PROMOCIONES_PK' (UNIQUE) (
Cost=1 Card=1)
5 2 VIEW
6 5 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
7 6 INDEX (RANGE SCAN) OF 'PR_PROMOCIONOFI_PK' (UNIQUE
) (Cost=2 Card=1 Bytes=39)
8 1 VIEW
9 8 SORT (GROUP BY) (Cost=3 Card=82 Bytes=3198)
10 9 TABLE ACCESS (FULL) OF 'IC_FICH_GRAFICOS' (Cost=1 Ca
rd=82 Bytes=3198)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
5 consistent gets
0 physical reads
0 redo size
918 bytes sent via SQL*Net to client
315 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
What it bothers me is
TABLE ACCESS (FULL) OF 'IC_FICH_GRAFICOS' (Cost=1 Card=82 Bytes=3198)
Since this table could be pretty big, meanwhile for table PR_PROMOCIONOFI does goes by index range scan and both these tables have similar number of rows probably only 5 or 6 rows difference :o
-
I think you cannot avoid the full table scan because you have used group by on 'IC_FICH_GRAFICOS.
Anyway I am thinking abt it.
-
However I do the same with PR_PROMOCIONOFI
using a group by it goes
SORT GROUP BY
INDEX RANGE SCAN PR_PROMOCIONOFI_PK
tricky!
-
Basically, I did not read all the detail in the thread. However, there were some obvious issues with the query as posted, so I just went right to it. Try:
SELECT
---A.*,
---B.OFICINAS,
---C.FOTOS
FROM
---ICDBA.IC_PROMOCIONES A,
---(
------SELECT
---------COUNT(*) AS OFICINAS,
---------COD_AGE,
---------COD_RED,
---------COD_PRM
------FROM
---------ICDBA.PR_PROMOCIONOFI
------WHERE
---------COD_RED---=---2---------AND
---------COD_AGE---=---596------AND
---------COD_PRM---=---3331 ------
------GROUP BY
---------COD_AGE,
---------COD_RED,
---------COD_PRM
---) ---------------------B,
---(
------SELECT
---------COUNT(*) AS FOTOS,
---------COD_RED,
---------COD_AGE,
---------COD_PRM
------FROM
---------ICDBA.IC_FICH_GRAFICOS
------WHERE
---------COD_RED---=---2---------AND
---------COD_AGE---=---596------AND
---------COD_PRM---=---3331 ------
------GROUP BY
---------COD_AGE,
---------COD_RED,
---------COD_PRM
---) ---------------------C
WHERE
---A.COD_RED---=---2---------AND
---A.COD_AGE---=---596------AND
---A.COD_PRM---=---3331 ------AND
---B.COD_AGE---= A.COD_AGE---AND
---B.COD_RED---= A.COD_RED---AND
---B.COD_PRM---= A.COD_PRM---AND
---C.COD_AGE(+)= A.COD_AGE---AND
---C.COD_RED(+)= A.COD_RED---AND
---C.COD_PRM(+)= A.COD_PRM---AND
- You are not restricting on B until after you grouped the entire table. You should restrict first, then group and save a lot of time. Same with B.
- Never count a specific column, it *won't* change your answer but can change your plan. You were counting using a column that you did not reference anywhere else in the query, meaning that the optimizer probably had to go back to the table just to get that value. And again, it *will not* change your answer from the COUNT() function [COUNT (DISTINCT ) matters, but not COUNT()]. That probably explains your table scan.
- Finally, I'll assume that there is an index on each table that contains COD_AGE, COD_RED and COD_PRM as the left, leading columns.
Anyway, try this and let me know what you find.
- Chris
-
Hi
The plan changed now to
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=445)
1 0 NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=445)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=393)
3 2 VIEW (Cost=1 Card=1 Bytes=52)
4 3 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
5 4 INDEX (RANGE SCAN) OF 'PR_PROMOCIONOFI_PK' (UNIQUE
) (Cost=2 Card=1 Bytes=39)
6 2 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
t=1 Card=246 Bytes=83886)
7 6 INDEX (UNIQUE SCAN) OF 'IC_PROMOCIONES_PK' (UNIQUE)
8 1 VIEW
9 8 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
10 9 INDEX (RANGE SCAN) OF 'IC_FIC_GRAFICOS_PK' (UNIQUE)
(Cost=2 Card=1 Bytes=39)
the other way was
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=445)
1 0 NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=445)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=393)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
t=1 Card=1 Bytes=341)
4 3 INDEX (RANGE SCAN) OF 'IC_PROMOCIONES_PK' (UNIQUE) (
Cost=1 Card=1)
5 2 VIEW
6 5 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
7 6 INDEX (RANGE SCAN) OF 'PR_PROMOCIONOFI_PK' (UNIQUE
) (Cost=2 Card=1 Bytes=39)
8 1 VIEW
9 8 SORT (GROUP BY) (Cost=3 Card=82 Bytes=3198)
10 9 TABLE ACCESS (FULL) OF 'IC_FICH_GRAFICOS' (Cost=1 Ca
rd=82 Bytes=3198)
The difference is that
TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
t=1 Card=1 Bytes=341)
changed to
TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
t=1 Card=246 Bytes=83886)
and now IC_FIC_GRAFICOS is accessed with index.
-
But did you actually run it and compare the logical reads (V$SQL_AREA.BUFFER_GETS) and timings of the 2 statements? Since you are now restricting the lists before the grouping, you will be saving an awful lot of effort. Unfortunately, the plans don't tell you everything :). Of course, the logical reads count will also be affected by the switch from a table scan to and index usage. Your best bet might be to run each statement twice to remove any caching issues and check the timing of the second runs for each.
- Chris
-
I will do these timing today busy yesterday coz solaris died :(
-
Your SQL statement:
(SELECT COUNT(COD_TIP_IMG) as fotos, COD_RED, COD_AGE, COD_PRM
FROM ICDBA.IC_FICH_GRAFICOS
GROUP BY COD_AGE, COD_RED, COD_PRM) C
Pando, Create a composite index on COD_AGE, COD_RED,COD_PRM columns, because if the index is available and the columns are mentioned in the Group By clause, the index will be used even though they are not mentioned in the WHERE clause.
With few hundred rows, it is very difficult to predict the performance. Populate 100,000 rows in all tables using LOAD RUNNER, and then do the tuning.
