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,

[Code]....

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Return 0 When Inline View Is Returning NULL

Sep 6, 2012

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.

View 2 Replies View Related

SQL & PL/SQL :: How To Make Return All Records Except Duplicates

Mar 29, 2010

I have a table like this basic example:

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.

View 1 Replies View Related

SQL & PL/SQL :: Query Must Return All Equivalent Records

Nov 26, 2012

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.

View 7 Replies View Related

SQL & PL/SQL :: Query To Return Value / String If No Records Found

Jun 17, 2012

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'

View 2 Replies View Related

PL/SQL :: Select Query - Return 0 When No Records Fetched

Jun 25, 2013

I have a select query select userid from user_tbl where name='hhh';    

I want to return 0 when no records are fetched and I want to handle both teh cases in a single query other than proc or function.

View 12 Replies View Related

Domain Index - Select Query With Clause Does Not Return Any Records

Jun 30, 2012

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?

View 2 Replies View Related

SQL & PL/SQL :: Rows Depends On A Total Field

Feb 24, 2012

I have a table TP having following data (Dashes used for space as i am unable to have proper alignment)

ID1-----TOT
1 ------- 5
2 ------- 7

I need a query that repeat the records depending on the field TP.TOT

e.g.

ID1 -----TOT
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
1 ------- 5
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7
2 ------- 7

View 5 Replies View Related

Underlying Devices That ASM Disk Groups Depends On?

Jun 21, 2012

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?

View 3 Replies View Related

Index Usage Depends On Columns Selected

Mar 6, 2013

somehow cannot understand why index is not used

in Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

1.     Included only indexed column and got a perfect plan

explain plan for2 select s.x_cnt
3 from reported_summary s
4 where s.x_cnt>0;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2674489506

-------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 306 (8)|
|* 1 | INDEX FAST FULL SCAN| S_NUI01 | 306 (8)|
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("s"."x_CNT">0)

2.     Included some other column and got TABLE ACCESS FULL

explain plan for2 select s.x_cnt,s.ru_id
3 from reported_summary s
4 where s.x_cnt>0;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335

---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("s"."x_CNT">0)

3.     Included all other columns and got TABLE ACCESS FULL as well explain plan for2 select s.x_cnt,s.* 3 from reported_summary s 4 where s.x_cnt>0;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335

---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("s"."x_CNT">0)

View 5 Replies View Related

Why A Certain Date Makes Query Faster

May 25, 2012

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?

View 7 Replies View Related

Index On Oracle 11g - Get Results Faster?

Nov 9, 2010

i have table (MEN) with 900,000 records.in this table i have field `IP` and `Tdate`.when i run query:

select * from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

it takes long time until i get Result.i try to make index like this:
create index
my_in
on
MEN (IP,Tdate );

but how to run the query to get fast Result?i try this:

select My_in from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

and get error: `ORA-00904`

View 1 Replies View Related

PL/SQL :: Truncating Or Drop Table Is Faster?

Jul 8, 2012

I have a table with 80 million records. Is dropping this large table is faster or truncating is faster?

View 6 Replies View Related

RMAN :: Restore / Recovery From Snapshot Faster?

Aug 8, 2012

Version : 11.2 on Solaris, AIX

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 ?

View 3 Replies View Related

Performance Tuning :: Query Sql Server Faster Than Oracle?

Feb 21, 2013

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.

View 1 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

Server Utilities :: Faster Impdp Excluding Indexes And Constraints

May 24, 2012

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

View 1 Replies View Related

SQL & PL/SQL :: Using Inline View?

Oct 6, 2012

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.

View 1 Replies View Related

SQL & PL/SQL :: Inline View?

Aug 19, 2013

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.

View 3 Replies View Related

SQL Inline Count

Jun 14, 2007

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?

View 1 Replies View Related

Application Express :: Second LOV Data Depends On Selected Data Of First LOV?

Jul 14, 2012

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

View 1 Replies View Related

Inline Query And Joins

Jul 31, 2012

When to use inline query and to use join.

View 3 Replies View Related

SQL & PL/SQL :: Using Count In Inline View

Sep 23, 2011

below query, first query is giving proper output i.e. 0 while second is giving wrong output i.e. 1.

Why this happen , what's wrong in using count in inline view ?

selecT count(*)
from (select *
from dual
where 1 = 2
union
select * from dual where 1 = 2);

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Create A Table Through Inline View?

Oct 1, 2010

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

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Inline View Degrade Performance

Apr 8, 2010

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.

Query :-
SELECT LEVEL_USER,
TRIAL_NO,
UNIT_NO,
COUNTRY_CODE,
PERSONNEL_NO,
[code].......

Note :- i have Attached the explain Plan for the same .Please let me know how can i improve performance of this Query.

View 3 Replies View Related

SQL & PL/SQL :: Correlated Query And Inline View

Apr 9, 2010

which one is better in performance point of view and why between inline view and correlated query.

View 5 Replies View Related

PL/SQL :: Correlated Update (Inline View)

Jan 21, 2013

Can I update inline view like below?

update

(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;

SQL Error: ORA-00904: "ANS"."ANSWER_ID":

View 8 Replies View Related

SQL & PL/SQL :: Convert Data To Table / Inline View

May 10, 2013

Is there a function that allows the following?

select SOME_FUNCTION('N','E','S','W') from dual;

That returns

N
E
S
W

Currently I'm just doing the following

WITH direction AS
(SELECT 'N' dir FROM DUAL
UNION
SELECT 'E' FROM DUAL
UNION
SELECT 'S' FROM DUAL
UNION
SELECT 'W' FROM DUAL)
SELECT *
FROM direction;

View 4 Replies View Related

SQL & PL/SQL :: How To Use Window Functions In Where Clause Without Inline View

May 16, 2013

Below is the query where I need to filter the data using window function.

I don't want to use inline view for mt query because i need to use the same query in OWB which it wont support.

Select a.Physical_Id,
a.Booking_Begin_Date,
a.Booking_End_Date,

[Code]....

I Cannot use below query in OWB Which is Inline View
----------------------------------------------------------------------------

Select b.* From
(Select a.Physical_Id,
a.Booking_Begin_Date,

[Code].....

View 2 Replies View Related

PL/SQL :: Inline View Vs Subquery - Technical Difference

Jul 7, 2012

After searching a lot on net the differences between inline view and subquery that i came across are

1) you can use order by in inline view but not in subquery.
2) you can use alias for inline view but not subquery.

i have to fetch the same outcome using both the functionality respectively, then what is the technical impact/better.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved