PL/SQL :: Records Return Faster Inline Rather Than With Or Depends On Situation
Jan 15, 2013
I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as
(
select person_uid, id, name, academic_period,
case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
Attached query is running fine if inline view B (Marked in Comments) returning value. If inline view B returns NULL then it fails to return result. I want if Inline view B is returning NULL then it should pass ZERO to main query.
ID Product Color Time-In 1 Apple Green May 2 Apple Red April 3 Pear Green May 4 Pear Green April 5 Plum Blue June
In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds
So I should get these
1 Apple Green May 2 Apple Red April 3 Pear Green May 5 Plum Blue June
If I do a select distinct then I will only get those fields I test on (product and color), not the rest.
I've stucked with a query. I have a table that i store the IDs of logically equal records.
For example; A = B B = C X = Y Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/ insert into temptable values(12,13);/ insert into temptable values(13,14);/ insert into temptable values(13,15);/
SELECT distinct ID1 from ( SELECT * FROM temptable START WITH ID1 = 13 OR ID2 = 13 CONNECT BY NOCYCLE ( (PRIOR ID1 = ID1) OR (PRIOR ID1 = ID2) OR (PRIOR ID2 = ID1) OR (PRIOR ID2 = ID2)) ) WHERE ID1 <> 13 union [code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
Having following table: UserID REC_TYP REC_CD 12345 'OFFR' 12 23456 'MSG' 13
I'd like to construct the query which in this particular case would return the REC_CD as 'Record_ID' for REC_TYP='OFFR' where USERID=? (always fetched by the application) and if such USER_ID doesn't exists (for the particular REC_TYP of course) to return string or any other value. e.g. The result for this query in case of user_id 23456 = would be "doesn't exist" or sth for instance 'FALSE' and for 123456 it would be '12'
I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.
-> will exec ctx_ddl.sync_index('index_name')'resolve my problem? -> since the view is a materialized view, how can i make sure that the latest data added are also picked up?
We have a big problem in the underlying devices that ASM disk groups depends on. We have SAN disks (EMC DMX) presented to us as /dev/sda, /dev/sdb, etc.These disks have actually multipath setup. For example, - /dev/emcpowera has two different paths as:
1./dev/sda 2./dev/sdp
We were using the direct path /dev/sda to format the disk (as fdisk /dev/sda), and then used oracleasm to create disks (oracleasm createdisk ASMDISKDATA0 /dev/sda1). Then, ASM disk groups were created with those lables (volumes), and then database was created using the ASM disk groups.
Now our platform folks are telling us that we should use the multipath /dev/emcpowera instead of direct path /dev/sda as the direct path is not guaranteed across reboot.
So the questions are:
1.Is there a way to re-link the disk group to asm disk to the multipath devices (/dev/emcpowera1 instead of /dev/sda1)? 2.Is this even an issue for ASM? If the /dev/sda fails after reboot, can Oracle ASM automatically discover the other path /dev/sdp to the physical EMC disk?
I have a query with a few tables in join, and I filter data with something like
where mytable.initial_date > sysdate-30
If I use any date, it takes about 5 seconds to run, but if I use
to_date('01010001','ddmmrrrr')
instead of any other dates, it takes just a few milliseconds. Now, it's just a curiosity, but why that date makes the query VERY fast? Does Oracle treat it in some special way? Maybe it knows every date is greatest than that date and doesn't consider the filter?
All our production DBs are backed up by some Hitachi tool (apparently it is called ShadowImage) . Apparently they put all the tablespaces in Backup mode and 'take a snap' . Our BAU team says this is faster.
On an event of DB Crash Is it easier to restore, recover from these 'snapshots' ? Are these snapshots much faster than RMAN ?
I have a query optimized as to it indexes and others runs immediately when the answer is few records in SQL Server such as Oracle, however when the result is large eg 20,000 records all data access times are very diferent. The query returns many fields (about 20) and some of them are of type Varchar 250 and some of 2000 I understand here may be the problem, but not is because for similar results (20,000 records) sql run in 2 seconds and Oracle but it responds little to have full data takes around 30 seconds. The problem is really in bringing information to all these fields since if the inquiry it also but only returning a numeric field is done in 2 seconds. Tests I've done them both through ODBC, in the Toad as in the own Oracle console on the server, so it is not problem Driver or flow of data through the network, I would like to think that this is some of the settings I think there is as much difference between Oracle and Sql. The databases are ORACLE 10 and SQL Server 2008.
We need to setup a test system using data from one of the offsite customer location.The option we would like to use is impdp with network link. Below given step will make import faster if we exclude indexes and constraints
Steps Import schema excluding constraints,rf_constraints,indexes with metadata only Import schema with data only spool ddl using dbms_metadata.get_ddl from Source for constraints,rf_constraints and indexes execute on Destination the Index creation ddl execute on Destination constraints and rf_constraints ddl
I am working on this assignment question for class:
Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices for each vendor (just one for each vendor). Use an inline view that returns MAX(invoice_total) grouped by vendor_id, filtering for invoices with a balance due
What I have coded so far is below SELECT i.vendor_id, To_char(SUM(invoice_total), '$9,999,999.99') AS sum_invoice_total FROM invoices i join (SELECT vendor_id,
[code]...
I am getting some results which I have attached. I'm not sure if I'm capturing the "largest unpaid invoices for each vendor" part of the question. Yielding to the knowledge the group and trying to use the proper posting etiquette. I have attached my output screen as well.
the execution steps of inline view?importance&advantage of inline view?.Also reference link where i can get more information about inline view(i.e. basics to advanced),exercises and interview questions.
we're having a few tables which queries about 10.000 articles. As we don't show them all at once we are using pagination and use the rownum to show only a limited number of the results.
Now as these queries are pretty complex we have to optimize them and since we use pagination we have to call our query twice (first we make a count(*) and then we call the small resultset of a few rows). Ofcourse we are looking for a solution to call it only once and still use the pagination. We could load the whole resultset of 10.000 results and let java show only a few but that makes our line between the oracle and webserver pretty heavy. Is there a way to call the total number of results and give back only a small resultset just in one query?
In my page, I have two items(type Popup LOV): P2_APP and P2_MOD and I've created two LOVs for each item. What I want is that when I select one value in first LOV in second LOV I'll get data that is related with select value in first LOV.
My table logic in database is ok, and select statements are alright.
I think that select statement in second LOV is not fetching data from first LOV item:
select MOD_NAME as display_value, MOD_CODE as return_value from MODS where APPLICATION= *:P2_APP* <-------- this is first LOV item with data previously selected order by 1
there is a diff. problem for me.when i create table through inline view then it shows 2246 records but if i check these records only in select statement then it shows 124 records. i cant understand how table shows 2246 records even then atual records in inline view shows only 124 records.
following is a query
create table sam as select * from (( select distinct stck.item_code from ( select item_code,bal
I am working on Oracle 10g and Below is my query which is taking 30min. to execute. I am using two inline view and then make joins on these inline view because of i think it Degrade Performance.
(select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a, (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))
set b.answer = a.answer;
My second question is: The following query is giving error. What can I do?
update answers ans set (ans.answer) = (with a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55), b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) select a.answer from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) where ans.main_group_id = 3;
