learnt something new
April 17, 2008 - 11am Central time zone
Reviewer: Sokrates
never saw the syntax in 2.
Could you please explain it any further.
Furthermore, it seems, that this syntax isn't supported anymore in V10:
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
SQL> create table SUBJECT(subject_id int, subject_name int);
Table created.
SQL> create table TITLE(title_id int, title_nm int, subject_nm int, publisher_nm int);
Table created.
SQL> select*
2 from subjects
3 where subject_id NOT Exists (select * from titles ) ;
where subject_id NOT Exists (select * from titles )
*
ERROR at line 3:
ORA-00920: invalid relational operator
Followup April 17, 2008 - 11am Central time zone:
i think the subject_id is "a mistake" there.
They set up the subjects and titles table.
put no data into titles
and observed "the same answer"
I think Shailesh has made a mistake
April 17, 2008 - 5pm Central time zone
Reviewer: rsergio from Brazil
Tom,
I'm not with my crystal ball right now, but I guess the author of the question intended to put the
second query as
select *
from subjects
where subject_id not in (select title_id from titles)
Put this way the queries look more similar, don't they?
Followup April 17, 2008 - 9pm Central time zone:
if so, they are still very different.
ops$tkyte%ORA9IR2> create table s ( subject_id number );
Table created.
ops$tkyte%ORA9IR2> create table t ( title_id number );
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into s values ( 1 );
1 row created.
ops$tkyte%ORA9IR2> insert into t values ( null );
1 row created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from s where not exists (select null from t where t.title_id =
s.subject_id);
SUBJECT_ID
----------
1
ops$tkyte%ORA9IR2> select * from s where subject_id not in (select title_id from t );
no rows selected
if the column you select in the subquery is NOT NULL - then not in and not exists are equivalent - and the optimizer knows that - and treats them "the same" basically.
If the column you select in the subquery is nullable - then NOT IN and NOT EXISTS are NOT interchangeable.
NOT IN and NOT EXISTS
April 17, 2008 - 6pm Central time zone
Reviewer: A reader
I believe he wants to know the difference between NOT IN and NOT EXISTS. The difference is to deal
with NULL for subject_id. I hope this helps, Tom.
Query
September 27, 2010 - 5am Central time zone
Reviewer: Aditya
employees whose salary greater than their average salary according to their dept in employee table
Followup September 27, 2010 - 12pm Central time zone:
the sky is blue and grass is green, but only in the spring and summer.
now what? did you mean to ask something?
September 28, 2010 - 9am Central time zone
Reviewer: Johny Alex
@Aditya,
I know that you meant the EMP and DEPT table. But please mention that very clearly when you ask
questions in the futuere.
I used to ask this kind of "Silly" questions some times back
Just follow the link, you will get the answer. (Point 12 of KEYS)
http://www.techfaq360.com/viewTutorial.jsp?tutorialId=327
Followup September 28, 2010 - 11am Central time zone:
well, not only that but:
"employees whose salary greater than their average salary according to their dept in employee table"
is a statement - not a question, not a specification.
I assume they mean:
for each employee in the EMP table, compare their salary to the average salary for the department they are in <and then something happens here>
Not sure what happens there - that is left out, the rest is an assumption. If they post the actual homework/quiz question they have - maybe we can take a look.
I just read those questions - ugh, that was painful. I could write a valid disagreement with many of them.
q14 pops out.
14. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.
the delete MIGHT result in deleting all of the rows - might not. There could be rows you don't see (other sessions have inserted but not yet committed). So the table may or may not be empty right afterwards. It could be that as your delete was executing a commit happened - which you would not see with your delete and the end result would be that there is still data in the table after you delete - however, truncate would never let that happen - it locks the table.
5. What is the parameter substitution symbol used with INSERT INTO command?
&
well, there is no such THING as a substitution symbol to be used with INSERT. There is however this thing called sqlplus which by default allows you to do some substitution using & by default, but I turn it off frequently - or set it to ^ so & is not always the answer for sqlplus either. However, as written the only correct answer is "there is not one"
2. What operator performs pattern matching?
LIKE operator
what about contains?
23. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
or drop a column
or drop a partition
or drop a subpartition
or drop all unused columns
or drop values for list partitions
or deallocate storage when you truncate
or ............................................
and there are more.... :(
September 28, 2010 - 11am Central time zone
Reviewer: Bixapathi
Hi Aditya ,
Here you go :
Q1:
select emp_name ,sal from emp e1 where e1.sal >(select avg(e2.sal) from emp e2 where
e1.dept_id=e2.dept_id)
Q2:
select e1.emp_name,e1.sal from emp e1, (select avg(sal) sal ,dept_id from emp e2 group by dept_id )
e2
where e1.sal>e2.sal and e1.dept_id=e2.dept_id
Q3:
select e.emp_name,e.sal from (select emp_name,sal,avg(sal) over (partition by dept_id) avg_sal from
emp ) e
where sal>avg_sal
all of the above work well BUT ......... just have a look at the Q3 - there is only ONE 'e' - no e1
and e2 - that's why Tom always says -"ANALYTICS ROCK and ROLL"
subquery
September 29, 2010 - 5am Central time zone
Reviewer: Bixapathi
Hi Tom,
I have been under the impression that the optimizer will treat the below two queries (Q1 and Q2) in
the same manner
because the Optimizer will convert the Q1 to Q2 internally as part of Query transformation and
infact if we see the two plans - both the plans are same - but why there are less logical i/os in
the Q2 ??
Just wanted to know about Q1 and Q2 - I know Q3 will be better than Q2 and Q3 .....
this testing done on 11gR2.
(On 9i- there are same logical i/os for Q1 and Q2)
Employee(emp_no pk,emp_name,sal,dept_no(fk references dept(dept_no))
Department(dept_no pk,dept_name)
they are very small tables (emp=11 rows and dept=6 rows)
Q1:
SQL> select emp_name ,sal from employee e1 where e1.sal >(select avg(e2.sal) from employee e2
2 where e1.dept_no=e2.dept_no);
Execution Plan
----------------------------------------------------------
Plan hash value: 439651729
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 38 | 8 (25)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 18 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEE | 11 | 66 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEE | 11 | 132 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPT_NO"="ITEM_1")
filter("E1"."SAL">"AVG(E2.SAL)")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
460 bytes sent via SQL*Net to client
337 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
Q2:
SQL> select emp_name ,sal from employee e1, (select avg(sal) avg_sal,dept_no from employee
2 group by dept_no ) e2 where e1.sal > e2.avg_sal and e1.dept_no=e2.dept_no
3 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2300762642
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 38 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 18 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEE | 11 | 66 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEE | 11 | 132 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."DEPT_NO"="E2"."DEPT_NO")
filter("E1"."SAL">"E2"."AVG_SAL")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
460 bytes sent via SQL*Net to client
337 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
Q3:
SQL> select emp_name ,sal from (select emp_name,sal,avg(sal) over (partition by dept_no) avg_sal
fro
m employee) e
2 where sal> avg_sal;
E
Execution Plan
----------------------------------------------------------
Plan hash value: 1555799450
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 473 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 11 | 473 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 11 | 132 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEE | 11 | 132 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">"AVG_SAL")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
460 bytes sent via SQL*Net to client
337 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
Followup September 29, 2010 - 9am Central time zone:
use tkprof, it'll show you the IO's step by step in the row source operation. compare those.
average salary
October 3, 2010 - 2am Central time zone
Reviewer: ROSHAN SAWARBANDHE from bhandara, maharashtra,INDIA
" select department number from emp who gets highest average salary from all the department"?
Followup October 4, 2010 - 2am Central time zone:
"the blue sky is red in the evening, for all people"
now what?
are you trying to ask something? Or just making a statement.
October 23, 2010 - 1am Central time zone
Reviewer: amardeep from india,delhi
hi tom i work in a big Software Company in India as a developer in pl sql.
i am looking for a job in ORACLE CORPORATION.
Can u help?
ReGARD'S
amardeep
amardeepasr@yahoo.co.in
Followup October 25, 2010 - 5pm Central time zone:
"U" isn't available, "U" is dead as far as I know. Look it up, it is true.
http://en.wikipedia.org/wiki/U_of_Goryeo
But that said, I can help. I can tell you that using "text messaging speak" like "u" and such makes you sound like a 12 year old. It is not very professional and will get you laughed at (only you won't know you were laughed at since the companies you try to get employment with simply won't contact you).
So, I would work on the communication skills - something very important as you approach people to ask them for opportunities. You never know what the person on the other end of the communication line will think of you - unless you approach them with a degree of professionalism from the very beginning.
Using full words - not "text messaging speak". Things like that are oh so very important.
That is the extent of my help - you can check oracle.com for job postings, we have a job website (click on careers from the home page) there.
Not exists & Not in
June 3, 2011 - 3am Central time zone
Reviewer: A reader
Hi Tom,
U gave this example for not exists and not in difference.
ops$tkyte%ORA9IR2> create table s ( subject_id number );
Table created.
ops$tkyte%ORA9IR2> create table t ( title_id number );
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into s values ( 1 );
1 row created.
ops$tkyte%ORA9IR2> insert into t values ( null );
1 row created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from s where not exists (select null from t where t.title_id =
s.subject_id);
SUBJECT_ID
----------
1
ops$tkyte%ORA9IR2> select * from s where subject_id not in (select title_id from t );
no rows selected
But when fire the query like select * from s where not exists (select null from t)
no rows selected.
Why is it so.. Does it mean even for null value not exists and not in behaves in the same manner. I
am new to database area. My apologies if its a silly query.
Correlated sub query with hierarchical
March 7, 2012 - 6am Central time zone
Reviewer: Somjit
Hi Tom,
I am facing a performance issue with the following code:
SELECT TRS.CycleId
FROM current_1218232753 XIBT_OUT, current_2113697630 TRS
WHERE To_Number(To_Char(XIBT_OUT.CreateDate,'YYYYMMDD')||REPLACE(XIBT_OUT.Createtime,':'))
BETWEEN To_Number('20120225000000') AND To_Number('20120225000020')
AND TRS.CycleId = (SELECT DISTINCT(Q.ParentCycleId) AS ROOT
FROM cyclelink Q
WHERE connect_by_isleaf = 1
AND Q.ParentObjectId = '2113697630'
-- TIX_EDIEL_TRS identity
START WITH Q.ChildCycleId = XIBT_OUT.CycleId
CONNECT BY NOCYCLE PRIOR Q.ParentCycleId = Q.ChildCycleId
AND Q.ParentObjectId = '2113697630' --
TIX_EDIEL_TRS identity
) ;
Execution plan:
----------------------------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
----------------------------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 1 | 84 | 1242M
(7)|999:59:59 |
| 1 | NESTED LOOPS | | 9 | 756 | 22
(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | CURRENT_1218232753 | 9 | 315 | 4
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_CURR1218232753_DATE | 2 | | 3
(0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | CYCLEIDCUR_2113697630 | 1 | 49 | 2
(0)| 00:00:01 |
| 5 | HASH UNIQUE | | 2 | 534 | 15
(7)| 00:00:01 |
|* 6 | FILTER | | | |
| |
|* 7 | CONNECT BY WITH FILTERING (UNIQUE)| | | |
| |
| 8 | TABLE ACCESS BY INDEX ROWID | CYCLELINK | 1 | 79 | 5
(0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | ChildCycleIdCycle_1 | 1 | | 4
(0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 206 | 9
(0)| 00:00:01 |
| 11 | CONNECT BY PUMP | | | |
| |
|* 12 | TABLE ACCESS BY INDEX ROWID | CYCLELINK | 1 | 79 | 4
(0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ChildCycleIdCycle_1 | 1 | | 3
(0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("CREATEDATE"),'YYYYMMDD')||REPLACE("CREATETIME",':'))>=2
0120225000000 AND
TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("CREATEDATE"),'YYYYMMDD')||REPLACE("CREATETIME",':')
)<=20120225000020)
4 - access("TRS"."CYCLEID"= (SELECT DISTINCT "Q"."PARENTCYCLEID" FROM "CYCLELINK" "Q" WHERE
CONNECT_BY_ISLEAF=1 AND "Q"."PARENTOBJECTID"=2113697630 START WITH
"Q"."CHILDCYCLEID"=:B1 CONNECT BY
NOCYCLE "Q"."CHILDCYCLEID"=PRIOR "Q"."PARENTCYCLEID" AND
"Q"."PARENTOBJECTID"=2113697630))
6 - filter(CONNECT_BY_ISLEAF=1 AND "Q"."PARENTOBJECTID"=2113697630)
7 - access("Q"."CHILDCYCLEID"=PRIOR "Q"."PARENTCYCLEID")
filter("Q"."PARENTOBJECTID"=2113697630)
9 - access("Q"."CHILDCYCLEID"=:B1)
12 - filter("Q"."PARENTOBJECTID"=2113697630)
13 - access("connect$_by$_pump$_004"."PRIOR Q.ParentCycleId "="Q"."CHILDCYCLEID")
Is there someway I can skip the correlated subquery and use some other alternatives?
|