OraFAQ Forum: Performance Tuning » Difference in retrieving number of rows between two queries

Home » RDBMS Server » Performance Tuning » Difference in retrieving number of rows between two queries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference in retrieving number of rows between two queries [message #135937]

Mon, 05 September 2005 00:23

sharan_it
Messages: 140
Registered: July 2005
Location: Chennai

Senior Member

Am trying to improve Performance of a Query..But the original and modified query dont have any diff. in the execution plan..But the modified query retrieves only onw row..BUt the original retrieved 24767 rows

I checked the subqueries of both the case..Subqueries of both queries gives the same value..Then why am getting diff. while i execute entire queries

Create Table Script of Table PRRP
---------------------------------

ALTER TABLE PRRP DROP PRIMARY KEY CASCADE;
DROP TABLE PRRP CASCADE CONSTRAINTS;

CREATE TABLE PRRP
(
PROV_C NUMBER(9),
PROV_C_EXT NUMBER(3),
FNDR_C VARCHAR2( Cool

,
FCBP_C_FDCN VARCHAR2(5),
FCBP_D_START DATE,
PRRP_D_START DATE,
PRRP_C_PAYEE VARCHAR2(9),
PRRP_F_ADJ VARCHAR2(4),
PRRP_D_END DATE,
PRRP_C_PYMT_OPT VARCHAR2(4),
PRRP_D_RCVD DATE,
PRRP_A NUMBER(8,2),
PRRP_C_STATUS CHAR(1),
PRRP_D_PAID DATE,
PRRP_C_CHK_NO NUMBER(9),
PRRP_C_BATCH NUMBER(5),
DIST_C NUMBER(2),
PRRP_N_PAYEE VARCHAR2(40),
FCRP_D_START DATE,
CCAD_C NUMBER(9),
PRRP_C_VENDOR VARCHAR2(12),
PRRP_C_METHOD VARCHAR2(2),
PRRP_C_EFT_TRAN_NO NUMBER(5),
CCAD_C_ID VARCHAR2(4),
PRRP_C_INVC NUMBER(9),
PRRP_D_INVC DATE,
CRT_USR VARCHAR2(25),
CRT_DATE DATE,
MOD_USR VARCHAR2(25),
MOD_DATE DATE,
PRRP_A_INC NUMBER(7,2),
PRRP_A_ADJ NUMBER(7,2),
PRRP_A_DEDUCT NUMBER(7,2),
PRRP_D_CHK DATE
)
LOGGING
NOCACHE
NOPARALLEL;

CREATE INDEX MZ_TMP_PRRP3 ON PRRP
(PROV_C, PROV_C_EXT, PRRP_C_STATUS, PRRP_C_CHK_NO, PRRP_D_CHK)
LOGGING
NOPARALLEL;

CREATE UNIQUE INDEX PK_PRRP ON PRRP
(PRRP_C_INVC, PRRP_D_START, PROV_C, PROV_C_EXT, PRRP_F_ADJ)
LOGGING
NOPARALLEL;

CREATE INDEX PRRP_IDX ON PRRP
(PROV_C, PROV_C_EXT, PRRP_D_START, FNDR_C, FCBP_C_FDCN)
LOGGING
NOPARALLEL;

CREATE INDEX PRRP_IDX1 ON PRRP
(PRRP_C_STATUS)
LOGGING
NOPARALLEL;

CREATE INDEX PRRP_IDX2 ON PRRP
(PRRP_N_PAYEE)
LOGGING
NOPARALLEL;

CREATE INDEX PRRP_IDX3 ON PRRP
(PRRP_D_PAID)
LOGGING
NOPARALLEL;

CREATE INDEX PRRP_IDX4 ON PRRP
(FCRP_D_START)
LOGGING
NOPARALLEL;

CREATE INDEX PRRP_IDX5 ON PRRP
(PRRP_C_STATUS, PROV_C, PROV_C_EXT, PRRP_C_PAYEE)
LOGGING
NOPARALLEL;

CREATE INDEX PRRP_IDX6 ON PRRP
(PRRP_C_STATUS, PROV_C, PROV_C_EXT, PRRP_D_RCVD)
LOGGING
NOPARALLEL;

DROP PUBLIC SYNONYM PRRP;

CREATE PUBLIC SYNONYM PRRP FOR PRRP;

ALTER TABLE PRRP ADD (
CONSTRAINT PK_PRRP PRIMARY KEY (PRRP_C_INVC, PRRP_D_START, PROV_C, PROV_C_EXT, PRRP_F_ADJ));

GRANT DELETE, INSERT, SELECT, UPDATE ON PRRP TO PUBLIC;

Create Table Script of Table Prov
---------------------------------

ALTER TABLE PROV DROP PRIMARY KEY CASCADE;
DROP TABLE PROV CASCADE CONSTRAINTS;

CREATE TABLE PROV
(
PROV_C NUMBER(9),
PROV_C_EXT NUMBER(3),
PROV_C_TP VARCHAR2(2),
PROV_N VARCHAR2(40),
PROV_L_STRT VARCHAR2(25),
PROV_L_STRT2 VARCHAR2(25),
PROV_L_CITY VARCHAR2(18),
PROV_L_ST VARCHAR2(2),
PROV_L_ZIP VARCHAR2(10),
PROV_L_MAIL_STRT VARCHAR2(25),
PROV_L_MAIL_STRT2 VARCHAR2(25),
PROV_L_MAIL_CITY VARCHAR2(18),
PROV_L_MAIL_ST VARCHAR2(2),
PROV_L_MAIL_ZIP VARCHAR2(10),
PROV_L_PH VARCHAR2(16),
PROV_L_PH_ALT VARCHAR2(16),
PROV_L_FAX VARCHAR2(16),
COUN_C VARCHAR2(3),
PROV_N_CNTCT1_1ST VARCHAR2(15),
PROV_N_CNTCT1_LST VARCHAR2(15),
PROV_N_CNTCT1_TITLE VARCHAR2(4),
PROV_N_CNTCT2_1ST VARCHAR2(15),
PROV_N_CNTCT2_LST VARCHAR2(15),
PROV_N_CNTCT2_TITLE VARCHAR2(4),
PROV_C_LEGAL_STAT CHAR(1),
PROV_C_LEGAL_ID VARCHAR2(15),
PROV_D_LEGAL_EXPIR DATE,
PROV_D_LEGAL DATE,
PROV_D_MONITOR DATE,
PROV_D_MONIT_NEXT DATE,
PROV_C_EXCPTD_TP VARCHAR2(4),
PTCZ_C VARCHAR2(5),
PROV_C_VENDOR VARCHAR2(12),
PROV_C_INFC CHAR(1),
PROV_D_ACTV_CCMS DATE,
PROV_D_INACTV_CCMS DATE,
PROV_C_INACTV_CCMS VARCHAR2(2),
PROV_D_ACTV_CCFP DATE,
PROV_D_INACTV_CCFP DATE,
PROV_C_INACTV_CCFP VARCHAR2(2),
PROV_D_ACTV_CCRR DATE,
PROV_D_INACTV_CCRR DATE,
PROV_C_INACTV_CCRR VARCHAR2(2),
PROV_F_ON_SITE CHAR(1),
SECT_C VARCHAR2(4),
AREA_C VARCHAR2(4),
PROV_L_CROSS_STRT VARCHAR2(50),
PROV_D_CREATE DATE,
PROV_D_UPDATE DATE,
PROV_C_STAFF VARCHAR2(9),
PROV_Q_AL_MIN NUMBER(2),
PROV_C_UA_MIN VARCHAR2(2),
PROV_Q_AL_MAX NUMBER(2),
PROV_C_UA_MAX VARCHAR2(2),
PROV_F_SUN CHAR(1),
PROV_F_MON CHAR(1),
PROV_F_TUE CHAR(1),
PROV_F_WED CHAR(1),
PROV_F_THU CHAR(1),
PROV_F_FRI CHAR(1),
PROV_F_SAT CHAR(1),
PROV_T_OPEN DATE,
PROV_T_CLOSE DATE,
PROV_F_TRANS_FHOME CHAR(1),
PROV_F_TRANS_THOME CHAR(1),
PROV_C_SCHED VARCHAR2(2),
PROV_Q_STAFF NUMBER(2),
PROV_N_ASSMNT_TOOL VARCHAR2(20),
PROV_Q_ASSMNT_SCORE NUMBER(4),
PROV_F_USDA CHAR(1),
PROV_C_COMPLAINT VARCHAR2(4),
PROV_F_NON_PROFIT CHAR(1),
PROV_F_SCC_CNTRCT CHAR(1),
PROV_F_SCC_VCHR CHAR(1),
PROV_X_SCHED VARCHAR2(50),
PROV_Q_DAY_MIN NUMBER(5),
PROV_Q_DAY_MAX NUMBER(5),
MUNI_C VARCHAR2(3),
PROV_D_LAST_RFRL DATE,
PROV_Q_COMPLAINT NUMBER(3),
PROV_C_TP2 CHAR(1),
PROV_C_SUB_TP CHAR(1),
CCAD_C_ID VARCHAR2(4),
PROV_N_LICENSEE VARCHAR2(40),
PROV_N_CNTCT3_1ST VARCHAR2(15),
PROV_N_CNTCT3_LST VARCHAR2(15),
PROV_N_CNTCT3_TITLE VARCHAR2(4),
CSWR_C VARCHAR2(12),
PROV_C_SSN VARCHAR2(9),
PROV_D_DOB DATE,
PROV_D_DOB_VERI DATE,
PROV_C_DOB_VERI VARCHAR2( Cool

,
PROV_F_W9 CHAR(1),
PROV_F_SPANISH_COMM CHAR(1),
PROV_C_SPOKEN_LANG VARCHAR2( Cool

,
PROV_F_DIR_DEPOSIT CHAR(1),
PROV_C_REGION VARCHAR2( Cool

,
PROV_N_CNTCT1_DESIG VARCHAR2( Cool

,
PROV_N_CNTCT2_DESIG VARCHAR2( Cool

,
PROV_L_EMAIL VARCHAR2(100),
PROV_C_LIC_VERI VARCHAR2( Cool

,
PROV_F_HANDBOOK CHAR(1),
PROV_D_HANDBOOK DATE,
PROV_F_F1099 CHAR(1),
PROV_D_LAST_INV_SENT DATE,
PROV_L_BILL_STRT VARCHAR2(25),
PROV_L_BILL_STRT2 VARCHAR2(25),
PROV_L_BILL_CITY VARCHAR2(18),
PROV_L_BILL_ST VARCHAR2(2),
PROV_L_BILL_ZIP VARCHAR2(10),
PROV_C_EMS_ID NUMBER(9),
PROV_C_EMS_VERI VARCHAR2( Cool

,
PROV_D_EMS_VERI DATE,
PROV_C_OP_STATUS VARCHAR2( Cool

,
CRT_USR VARCHAR2(25),
CRT_DATE DATE,
MOD_USR VARCHAR2(25),
MOD_DATE DATE
)
LOGGING
NOCACHE
NOPARALLEL;

CREATE UNIQUE INDEX PK_PROV ON PROV
(PROV_C, PROV_C_EXT)
LOGGING
NOPARALLEL;

CREATE INDEX PROV_IDX1 ON PROV
(PROV_N, PROV_C, PROV_C_EXT)
LOGGING
NOPARALLEL;

CREATE INDEX PROV_IDX_LICNUM ON PROV
(PROV_C_LEGAL_ID)
LOGGING
NOPARALLEL;

CREATE INDEX PROV_IDX_SRS1 ON PROV
(PROV_C, PROV_C_EXT, CCAD_C_ID)
LOGGING
NOPARALLEL;

CREATE INDEX PROV_IND ON PROV
(PROV_C)
LOGGING
NOPARALLEL;

DROP PUBLIC SYNONYM PROV;

CREATE PUBLIC SYNONYM PROV FOR PROV;

ALTER TABLE PROV ADD (
CONSTRAINT PK_PROV PRIMARY KEY (PROV_C, PROV_C_EXT));

GRANT DELETE, INSERT, SELECT, UPDATE ON PROV TO PUBLIC;

Original Query
--------------


[ALIGN=center]select prrp.prov_c, prrp.prrp_c_chk_no, prrp.prrp_d_chk, prrp.prrp_a, prov.prov_f_dir_deposit from prrp, prov
where prrp.prov_c = prov.prov_c
and prrp.prov_c_ext = prov.prov_c_ext
and prrp_c_status = '7'
and prov.prov_d_actv_ccms is not null
and prov.prov_d_inactv_ccms is null
and prrp_c_chk_no = (select max(c.prrp_c_chk_no) from prrp c
where c.prov_c = prrp.prov_c
and c.prov_c_ext = prrp.prov_c_ext
and prrp_d_chk = (select max(b.prrp_d_chk) from prrp b
where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext));[/ALIGN]

This query retrieves 24767 rows

Execution Plan
--------------

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 898
FILTER
NESTED LOOPS 1 38 898
TABLE ACCESS FULL PRRP 1 22 897
TABLE ACCESS BY INDEX ROWID PROV 25 K 405 K 1
INDEX UNIQUE SCAN PK_PROV 25 K
SORT AGGREGATE 1 17
INDEX RANGE SCAN MZ_TMP_PRRP3 1 17 3
SORT AGGREGATE 1 12
INDEX RANGE SCAN MZ_TMP_PRRP3 13 156 3

Modified Query
--------------


1 select prrp.prov_c, prrp.prrp_c_chk_no, prrp.prrp_d_chk, prrp.prrp_a, prov.prov_f_dir_deposit from prrp, prov
2 where prrp.prov_c = prov.prov_c
3 and prrp.prov_c_ext = prov.prov_c_ext
4 and prrp_c_status = '7'
5 and prov.prov_d_actv_ccms is not null
6 and prov.prov_d_inactv_ccms is null
7 and prrp_c_chk_no = (select max(c.prrp_c_chk_no) from prrp c
8 where c.prov_c = prrp.prov_c
9 and c.prov_c_ext = prrp.prov_c_ext
10 and prrp_d_chk = (select x.value from (select max(b.prrp_d_chk) value from prrp b,prrp
11 where b.prov_c = prrp.prov_c
12* and b.prov_c_ext = prrp.prov_c_ext) X))

C2K@CTPROD> /

PROV_C PRRP_C_CHK_NO PRRP_D_CH PRRP_A P
--------- ------------- --------- --------- -
88907 1159101 14-APR-04 -.03 N

This query retrives only one row

Execution Plan
--------------

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 898
FILTER
NESTED LOOPS 1 38 898
TABLE ACCESS FULL PRRP 1 22 897
TABLE ACCESS BY INDEX ROWID PROV 25 K 405 K 1
INDEX UNIQUE SCAN PK_PROV 25 K
SORT AGGREGATE 1 17
INDEX RANGE SCAN MZ_TMP_PRRP3 1 17 3
VIEW 1 9 8195
SORT AGGREGATE 1 17
MERGE JOIN 4 M 77 M 8195
SORT JOIN 380 K 4 M 5196
INDEX FAST FULL SCAN MZ_TMP_PRRP3 380 K 4 M 164
SORT JOIN 380 K 1 M 2999
INDEX FAST FULL SCAN PRRP_IDX6 380 K 1 M 138

select max(c.prrp_c_chk_no) from prrp c,prrp
where c.prov_c = prrp.prov_c
and c.prov_c_ext = prrp.prov_c_ext
and prrp.prrp_d_chk = (select x.value from (select max(b.prrp_d_chk) value from prrp b,prrp
where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext) X)

C2K@CTPROD> /

MAX(C.PRRP_C_CHK_NO)
--------------------
1159101

But see the values return by first subquery in both queries

select max(c.prrp_c_chk_no) from prrp c,prrp
where c.prov_c = prrp.prov_c
and c.prov_c_ext = prrp.prov_c_ext
and prrp.prrp_d_chk = (select max(b.prrp_d_chk) from prrp b
where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext)

C2K@CTPROD> /

MAX(C.PRRP_C_CHK_NO)
--------------------
1159119

In these queries,parent query is same and only sub queries are different...But,individually second subqueries gives same value..Then how is it possible to get different values from two entire queries...

See the what sub-queries return....


select max(b.prrp_d_chk) from prrp b,prrp
where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext

C2K@CTPROD> /

MAX(B.PRR
---------
14-APR-04


select x.value from (select max(b.prrp_d_chk) value from prrp b,
prrp where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext) X

C2K@CTPROD> /

VALUE
---------
14-APR-04

Report message to a moderator

Re: Difference in retrieving number of rows between two queries [message #135970 is a reply to message #135937]

Mon, 05 September 2005 03:20

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

This is because 2 queries yields the same different result as one is normal subquery(Modified one) and another is correlated subquery(Original one).

You said:

Quote:

In these queries,parent query is same and only sub queries are different...But,individually second subqueries gives same value..Then how is it possible to get different values from two entire queries...
See the what sub-queries return....

select max(b.prrp_d_chk) from prrp b,prrp
where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext

C2K@CTPROD> /

MAX(B.PRR
---------
14-APR-04

select x.value from (select max(b.prrp_d_chk) value from prrp b,
prrp where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext) X

C2K@CTPROD> /

VALUE
---------
14-APR-04

2 nd query is in your format. But for 1st query to be in your format it should be like...

select prrp_d_chk from prrp where prrp_d_chk=
(select max(b.prrp_d_chk) from prrp b
where b.prov_c = prrp.prov_c
and b.prov_c_ext = prrp.prov_c_ext)

Do U think Both of the below given queries gives the same result ???

SQL> select x.sal from (select max(e1.sal) sal from emp e1,emp
  2  where e1.deptno=emp.deptno )x;

       SAL
----------
     11000

SQL> select sal from emp where sal=
  2  (select max(sal) from emp e1 where e1.deptno=emp.deptno);

       SAL
----------
      4000
      5000
      9000
     11000

SQL>