Code: Select all
select count(*) from
(
SELECT A.*, B.*, C.* FROM TABLE_A, TABLE_B, TABLE_C
WHERE A.XYZ=B.XYZ AND B.MNO=C.MNO
AND B.EFFDT BETWEEN C.EFFDT AND C.ENDDT
)
Moderators: chulett, rschirm, roy
Code: Select all
select count(*) from
(
SELECT A.*, B.*, C.* FROM TABLE_A, TABLE_B, TABLE_C
WHERE A.XYZ=B.XYZ AND B.MNO=C.MNO
AND B.EFFDT BETWEEN C.EFFDT AND C.ENDDT
)
Code: Select all
set pages 0 lines 32767 trimspool on termout off
@a.sql
set termout on
Many thanks for busting the myth. Now, I can confront one of my buddies who still believes in it.rleishman wrote:The SELECT COUNT(1)thing is a myth.
I thought this was the point. Everything else was just... tangential.kcbland wrote:This posters problem is the all-in-one job that new users always write. They never understand that their transformation pipeline runs at the speed of the slowest consuming process. In this case, it's obviously the receiving database putting back-pressure all the way up the pipeline.
Code: Select all
1 explain plan set statement_id=' 190427' for
2 select *
3 from ef_actl_expns
4 where src_sys > 'J'
5* order by src_sys, ldgr_id
Explained.
4 SELECT STATEMENT (Optimizer='CHOOSE')
1 TABLE ACCESS (BY GLOBAL INDEX ROWID) of 'EF_ACTL_EXPNS'[EF_ACTL_EXPNS@SEL$1] (1 rows)
1 INDEX (RANGE SCAN) of 'EF_AEXP_PK'[EF_ACTL_EXPNS@SEL$1] (1 rows)
Code: Select all
1 explain plan set statement_id=' 190427' for
2 select *
3 from ef_actl_expns
4* where src_sys > 'J'
Explained.
4 SELECT STATEMENT (Optimizer='CHOOSE')
1 TABLE ACCESS (BY GLOBAL INDEX ROWID) of 'EF_ACTL_EXPNS'[EF_ACTL_EXPNS@SEL$1] (1 rows)
1 INDEX (RANGE SCAN) of 'EF_AEXP_PK'[EF_ACTL_EXPNS@SEL$1] (1 rows)
Code: Select all
SELECT MIN(col1), MIN(col2), ....
FROM (
SELECT col1, col2 ....
..... rest of your query ....
)
Code: Select all
SET AUTOTRACE TRACEONLY