SQL & PL/SQL :: Does Ascending Index Ensures That Query Without Order By Will Have Result Set Sorted

Jul 4, 2013

Does ascending index ensures that query without order by will have the result set sorted?

E.g. the query is

select * from table_t where odm_type='I' and odm_uid>nvl(OpUidParm,-1);
column odm_type has index created like this (default is ASC):
create index ODM_UID_I on table_t (ODM_UID);

Will such a query always return the first record having the minimal odm_uid in all Oracle versions?

View 4 Replies


ADVERTISEMENT

Forms :: Migrating From Oracle 8i To 10g - How To Get Result In Ascending Order

Aug 31, 2010

I have migrated from Oracle 8i (8.1.7) to Oracle 10g, but when I execute a query in 8i without any order by clause, I get a result in ascending order. The same query when executed in 10g gives a result which is not ordered. How to get an order result in 10g. There are many forms and reports which use lov which are not ordered. Can I set the ordering at the database, so that I do not have to alter all the forms and reports.

I have also migrated my forms from 5 to 6, but the combo box in some forms in 6i do not appear at run time. How I can solve this problem. I have attached an forms5 .fmb file/.

View 7 Replies View Related

Bring Record To Top Without Changing Ascending Order Of Dep-id?

Jul 18, 2010

i have a table named employees in which there are 2 columns dep_id and name. One record having name as annie has dep_id=null .

When i arrange the records of the table in ascending order using order by dep_id the record with name annie and dep_id is placed in the last because null is consired largest. How can I bring that record to the top without changing the ascending order of the dep_id.

View 7 Replies View Related

Reports & Discoverer :: Not Giving Output In Sorted Order

Aug 18, 2010

i hav create a report and use order by in the query of the report when i run my query on sql prompt it gives output in a sorted manner but when i am using it in report builder then it is not giving the output in sorted order.

View 2 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 Replies View Related

SQL & PL/SQL :: Rank Result With Respect To Order

Feb 1, 2012

I have a SQL statement that return the following result.

col1
----
a
a
b
b
a
c
c
c
c

What i need is to eliminate the duplication and return the result as the following.

col1
----
a
b
a
c

Using group by or distinct will eliminate the third row "a" and will not return it ordered.My idea is to generate another column that rank the result as following.

col1rank
------------
a1
a1
b2
b2
a3
c4
c4
c4
c4

so using the following SQL query will return the result as needed

select col1, rank
from T
group by col1, rank
order by rank;

col1rank
------------
a1
b2
a3
c4

View 8 Replies View Related

9.2.0.8.0 Oracle Index Not Used In Order By Desc?

Oct 30, 2011

There are 2 setup of Oracle DB 9.2.0.8.0. There is a table T1 in both (one that is working 6.5 million rows and the other that is not working having 6.7 million rows). while doing - select * from t1 order by c1 desc; one is using index i1 and other is going for full table access.

View 1 Replies View Related

SQL & PL/SQL :: What Should Be Order Of Columns While Creating B-tree Non-unique Index

Jul 19, 2012

What should be the order of columns while creating b-tree non-unique index.

Low cardinality first
or
Hight cardinality first

View 15 Replies View Related

Performance Tuning :: Index Usage In Order By Clause On Nullable Column

Jan 28, 2011

I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'

So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting

here are the steps

Initially I kept the column Nullable

SQL> create sequence s5;
Sequence created.

SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001;
Table created.

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

SQL> create index i5 on t5(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T5',cascade=>true);
PL/SQL procedure successfully completed.
exit

SQL> alter session set events '10046 trace name context forever, level 12';

select *
from
t5 where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.00 0 16 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 49.49 49.72
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 69 0.00 0.00
SQL*Net message from client 69 22.89 28.04

Now I modified the 'id' column to Not Null

SQL> alter table t5 modify id not null;

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

COLUMN_NAME N
------------------------------ -
ID N
OWNER Y
OBJECT_NAME Y
SUBOBJECT_NAME Y
OBJECT_ID Y
DATA_OBJECT_ID Y
OBJECT_TYPE Y
CREATED Y
LAST_DDL_TIME Y
TIMESTAMP Y
STATUS Y
TEMPORARY Y
GENERATED Y
SECONDARY Y

14 rows selected.

select *
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 29 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 45 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 37.74 37.91
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 8.28 8.45

select id
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 6 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 6 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us)
1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 1.88 1.89

Questions are

1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?

View 5 Replies View Related

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

Nov 19, 2010

I have a query which had a join:

a.c1=b.c1 and a.c2=@var

where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like

a.c1(+)=b.c1 and nvl(a.c2,@var)=@var

This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')

But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?

View 2 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

Application Express :: 4 - Saved Reports Are Not Sorted?

Jul 27, 2012

When users are selecting a saved report on a given Interactive Report, they must search the entire list of saved reports for that IR because the choices are not sorted. Is there a way to improve the order that the choices are displayed in?

View 2 Replies View Related

PL/SQL :: Query To Get Result Set

Feb 12, 2013

I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records

SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
     " ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
    LKP_Item.Group, LKP_Item.Modality Source
    FROM ((LKP_Product
[code]....

T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp

Just want to know if there is any possibility of running the query with all the Groups in one go

Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.

View 1 Replies View Related

Sort Ascending Or Descending Of Last Total Column Of Matrix Report R6i

Nov 3, 2010

How can I Sort Ascending or Descending of Last Total Column of Matrix Report R6i i.e. F_SumsalPerempno, summing total salary of each empno at end of each row.

I need the employee paid highest amount of total salary during the year to appear on first row, while months to display as per original order.

View 2 Replies View Related

Concatenating Result Of Query

Aug 30, 2007

My scenario is something like this:

MyTable

Rownum Colval
1 23
2 34
3 45

I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?

View 2 Replies View Related

SQL & PL/SQL :: Query Result Into Excel Or CSV

Jun 15, 2011

I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.

View 4 Replies View Related

SQL & PL/SQL :: Query Result In One Line

Aug 29, 2011

I have a select that return the query like this:

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br

so,I need the query result in just one line...

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br

I saw some exemples of "pivot query" but I still looking for a solution.

View 10 Replies View Related

SQL & PL/SQL :: Different Result On Every Execution Of Query?

Jan 13, 2011

when I am executing the below query getting diffrent count every time and not able to guess what is happening.

SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day

View 6 Replies View Related

SQL & PL/SQL :: Query Yields No Result

Mar 20, 2012

in my table there is a row as below.

CITY_ICITY_NST_PROV_CPSTL_CUPDT_USER_IUPDT_TSUPDT_PGM_I
342DC79a03654822-FEB-12 02.02.13.410000000 PMOMR

when i use below below query, there are no values returned.

select city_i from city_e where (trim(city_n))= 'DC' and st_prov_c=79 but while i use below query, im bale to get the result

select city_i from city_e where city_n='DC' and st_prov_c=79

View 5 Replies View Related

Result Of Expensive Query

Jul 11, 2012

I need a a query which will give the result of most expensive query i.e. which is taking most of I/O /memory.

View 3 Replies View Related

SQL & PL/SQL :: Wrong Result For Query With Like And %?

Jul 21, 2012

I have a strange problem with query with like and %.

When I run this script:

ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
INSERT INTO TEST1 VALUES ('gsdk');

[code]....

I get this:

K1
ŁFa
ła
Śab <- WRONG
Śrrrb <- WRONG
4 rows selected

When i change datatype in column to varchar2 this code work correct.

The execution plan:

PLAN_TABLE_OUTPUT
SQL_ID d3d64aupz4bb5, child number 2
select * from TEST1 where k1 like N'Ł%'
Plan hash value: 4122059633
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 2 (100)
* 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01

[code]....

View 7 Replies View Related

Query To Display Records In Particular Order

Feb 8, 2007

I need to write a query to sort the records in a particular order,

Say if I group the records by Dept number
Dept no Name
10A
10G
10f

20B
20K

30I
30M
30R
30Y

I need to write a query that will make this records listed like

Dept No Name
10A
20B
30I

10G
20K
30M

10F
20null
30R

10null
20null
30Y

View 1 Replies View Related

SQL & PL/SQL :: Query Against EBS Order Lines And Headers

Sep 6, 2012

I want added one new column in the below report which will shows only the items purchased on Cash basis but when i am running the query the column not showing any data.

The query is :

Select DISTINCT ou.name||' Spare Parts' ORG, oh.order_number, trx.trx_number, trx.trx_date, p.party_name CUSTOMER, l.ordered_item,
/* ( Select Distinct it.description From MTL_SYSTEM_ITEMS_B it Where it.inventory_item_ID = l.inventory_item_id and it.organization_id = o.organization_id) Item_Desc, */
l.ordered_quantity, l.line_number,
l.inventory_item_id, l.unit_list_price , ----
a.account_number CST_NO ,
oh.Attribute1 MODEL , oh.Attribute2 SERL_NO, oh.attribute3 PAYM_MODE ,
u.user_name Prep_By, oh.CUST_PO_NUMBER PO_NUM,
[code]......

View 1 Replies View Related

Default In (order By) Select Query Different In 11g From 10g?

Nov 12, 2013

if when you are querying a table in 11g and you use the order by clause and there is more than one occurrences with the same values in the order by, if the 11g default is different than from 10g.

For instance. 

DECLARE MHBulk CURSOR FOR                          select invoice_nbr,               customer_nbr,                post_century,                post_yymmdd,     from CUSTOMERS     where customer_nbr = 1234     order by           post_century,           post_yymmdd; 

If you have more than one occurrence of the same customer_nbr, post_century, and post_yymmdd

it looks like the default order for how 11g retrieves the records is a bit different than the 10g default. 

View 9 Replies View Related

PL/SQL :: Limit Order By Rows Without Using Sub Query

Sep 4, 2013

Here is my query to fetch only 10  records from order by result set. 

select *from (select * from EOE_POC.PRODUCT_TEST_REPORT where PRODUCT_CODE='214d' order by CREATE_DATE desc ) where ROWNUM <= 10  

I am having problem binding it with java API . how to query this without using sub query ?

View 11 Replies View Related

SQL & PL/SQL :: How To Execute One Query Result In Another At Runtime

Dec 14, 2011

I have a query, when i run this this will give another sql statement. I want run this dynamically...

How to proceed?

View 10 Replies View Related

SQL & PL/SQL :: Why Sub Query Factoring Produces Different Result

Aug 19, 2010

select 1 from dual
where 1 >= any (select 1 as col from dual
union all
select 1 from dual
union all
select 1 from dual);

Output:
------
1
-
1
[code]....

Why the sub query factoring produces different result.Is sub query factoring can be a problem and leads to different result?

View 27 Replies View Related

SQL & PL/SQL :: Inserting Result Set Of Query Into Another Table

Jul 24, 2012

CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,

[Code]...

NAME PRICE DATAENTRD ROWRANK

aaa 9999 24.07.2012 05:56:00 1

aaa 10000 24.07.2012 05:55:58 2

I want to insert this result into another table, how can I do it??

Quote:TABLE

CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)

View 8 Replies View Related

SQL & PL/SQL :: Query Result More Row Return In Oracle

Apr 3, 2013

I have a view base on this query returned more rows returned.

select *
From (
Select c_code ,
from_date,
c_range ,
[code].........

View 5 Replies View Related

PL/SQL :: Send Query's Result Via Utl_stmp?

May 25, 2013

11.2.0.3 got the send mail code from [URL]

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
p_from      IN VARCHAR2,
p_subject   IN VARCHAR2,

[Code].....

/I want to call send_mail to pass a query output to "p_message": as example: select sid, program,module,machine from v$session where program='XXX';

And have been struggling due to lack of experience in PLSQL.

View 18 Replies View Related







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