SQL & PL/SQL :: Meaning Of Partition By Null In Analytic Functions

Aug 18, 2013

What's the meaning of "partition by null" in analytic functions like

select emp_no, dept_nm, count(*) over(partition by null) cnt
from some_table

is it right there is no partition?

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Analytic Functions?

Jul 25, 2006

analytic functions regarding the ORDER BY part =)

SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc) rank
4 from emp)

[code]...

why is that i just added ename on the ORDER BY part of the DENSE_RANK and then
SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc, ename) ran

[code]...

ADAMS and WARD we're removed from the result, why is it? did it rank it as UNIQUE per sal and ename?

View 9 Replies View Related

SQL & PL/SQL :: Rewriting Without Analytic Functions

Nov 12, 2011

How can I rewrite this without the analytic functions?

SELECT employee_ID, first_name, salary,
RANK() OVER(ORDER BY salary desc) toprank_desc,
RANK() OVER(ORDER BY salary ASC) toprank_asc
FROM employees
ORDER BY first_name

View 2 Replies View Related

SQL & PL/SQL :: How To Use Analytic Functions In Case

Mar 18, 2010

DATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.

DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3

DATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.

DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3
INSERT INTO TEMP_TEST

[code]...

View 1 Replies View Related

Analytic Functions To The Rescue

Oct 21, 2010

I have a table (events) with this structure: customer_id, event_id, ... For each customer_id there can be several rows in the table. I need to run a query of the format: select customer_id, expensive_function(customer_id),... from events.

The expensive_function to be applied to customer_id in the query is really expensive (a Java class calculating a check sum) and the events table has billions of rows.

Rows in events table have same customer_id for a few rows, then continue with a different customer_id ang again coming back to the first, etc.

I was thinking that it should be a way to trigger calculation of expensive_function only when customer_id changes, in order to reduce the number of calls. Only my knowledge about SQL is not going that far and I cannot use PL/SQL or any other procedural language, need to stick to standard SQL (or Oracle version of it).

View 7 Replies View Related

Analytic Functions / TEMP Usage?

Jan 26, 2011

I am building a reporting table using the count analytic function in order to count up several different attributes in one statement.What I find is that this method quickly eats up my TEMP space. This is 10gR2. I have attempted to use MANUAL workarea policy with as large ofsort_area_size as possible (2G) but that does not seem to have any effect on performance or TEMP usage. The RAW table is about 12G with 75 million rows. I am not that concerned about execution time, but rather TEMP usage.

--INSERT into <object>...
select distinct
file_sid,filename,control_numb,processing_date,file_class,
vendor_id,vendor_desc,
c_status_id,c_status_desc,

[code]...

I am not seeing any increase in onepass or multipass executions on the PGA during execution of this statement using...

SELECT CASE WHEN low_optimal_size < 1024*1024
THEN to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(high_optimal_size+1)/1024|| 'kb'
ELSE to_char(low_optimal_size/1024/1024,'999999') ||

[code]...

I'd like to get a better explaination of how analytics use the instance resources and TEMP space. For example if I add
a count with a different window (such as the last two columns commented in the above query) I blow out my temp space (70G).
Is the critcal factor the use of distinct? or multiple windows? or something else?

View 2 Replies View Related

SQL & PL/SQL :: Analytic Functions And Ranking / Ordering?

Jul 17, 2008

I need to return an ordered list of documents. The documents may belong to a set id (optional) and if so, are either a "master" or a "duplicate" type. For each set there can be only one master but many duplicates. My goal is to group all the sets together such that each master is proceeded by its duplicates.

Table description:
document_master_duplicates
(
documentid,
duplicate_setid,
is_master
)

This needs to join to another table briefcase_documents which contains our set of documents. The briefcase / document relationship is many-to-many.

Table description:
briefcase_documents
(
briefcaseid,
documentid
)

There's also a documents table containing the documentid and among other things a page_count. In the following example I want to sort the documents first by page count but preserving the master/dupe grouping. Any documents which don't belong to a set or are just a duplicate without a master i want at the end of my set but also ordered by page count.

Here's an example set that I would want to order by:

DocumentId Page_Count SetId Is_Master
2002 2 1 0
2003 20 2 0
2008 20 NULL NULL
2010 20 4 0
2012 1 4 1
2001 5 1 1
2004 16 3 1
2011 17 4 0
2014 10 5 0
2009 9 NULL NULL

As you can see I have a little bit of everything here. Docs 2001 and 2002 are the typical set of 1 master and its duplicate. Docs 2010, 2011, and 2012 is the same just a set of 3. Doc 2004 is a master but without any duplicates. Docs 2003 and 2014 are duplicates without a master (these docs have a master in the table but that doc isn't in the set i need to order by). Docs 2008 and 2009 do not belong to a set and as such do not have a master/dupe type.

The result i'm looking to achieve will be ordered as follows:

DocumentId Page_Count SetId Is_Master
2012 1 4 1
2011 17 4 0
2010 20 4 0
2001 5 1 1
2002 2 1 0
2004 16 3 1
2009 9 NULL NULL
2014 10 5 0
2003 20 2 0
2008 20 NULL NULL

As I said above I first want to get the groupings of master/dupes and order ascending on the masters page count. For each duplicate of a master I then want to order the duplicates by page count. After I finished ordering all the master/dupe groups I then want to move on to the rest of the documents which will contain documents that don't belong to a set along with documents which are duplicates but have no master in my set. However, documents which are masters but without duplicates should have been ordered along with the other master/dupes groupings.

With this all in mind I have just been completely overwhelmed as to where to even start. Am I using analytic functions? Hierarchical stuff?

View 10 Replies View Related

Analytic Functions - Pretend Result

Feb 24, 2012

I have a question regarding analytic functions. I've been working with some functions, but I can't achieve the one which gives me the pretend result. I know to resolve this without using a function, with a internal select, but I think the analytical function is faster and proper.

I've got the following data:

Brand Qt
A 150
B 200
C 50
D 100

I wanna be following output;

Brand Overall %
A 30
B 40
C 10
D 20

View 2 Replies View Related

SQL & PL/SQL :: Rewrite Query With NOT EXISTS Using Analytic Functions

Aug 25, 2011

How to re-write sql with NOT EXISTS using analytic functions?I have the following sql:

SELECT f1, f2
FROM t1 A2
WHERE NOT EXISTS (SELECT 1 FROM t1 A3 WHERE A3.f1=A2f1 AND A3.f2=A2.f2
AND A3.f3=A2.f3 AND A3.f4 = 0
)

View 6 Replies View Related

SQL & PL/SQL :: Purpose Of Over And Partition By Keywords In Analytical Functions

Jan 3, 2012

what is the purpose of over and partition by keywords in analytical functions

View 3 Replies View Related

Meaning Of Sentence SRW.USER_EXIT?

Apr 9, 2013

I am developing in the Report tool.I wonder what the meaning of the sentence SRW.USER_EXIT, and arguments that are used in it.

For example,

srw.user_exit('FND FLEXIDVAL
CODE=" "
NUM=" "
APPL_SHORT_NAME=" "
DATA=" "
VALUE=" "
DISPLAY=" "
DISPLAY=" "
IDISPLAY=" "
IDISPLAY=" "');
return();

View 2 Replies View Related

SQL & PL/SQL :: Escape Character Meaning

Jul 6, 2010

i want to understand each and every concept of oracle.in this book they explained about escape character, but stiil i cant get it..i want to understand why used escape character in 2nd query and whats its effects...

1. SQL> SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'Su%'
AND last_name NOT LIKE 'S%';

2. SELECT job_id, job_title
FROM jobs
WHERE job_id like 'AC\_%' ESCAPE '';

View 9 Replies View Related

Forms :: Meaning Of System Message_level

Jul 3, 2010

what is mean by system.message_level..

View 2 Replies View Related

Replication :: Meaning Of BOUNDED RECOVERY In Golden Gate

Jan 8, 2012

whats the meaning of BOUNDED RECOVERY in golden gate. I just know RECOVERY in oracle.. What is the difference when you say BOUNDED RECOVERY.

View 1 Replies View Related

SQL & PL/SQL :: Dropping Sub-partition / Range-partition And List Sub-partition

May 28, 2010

i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.

View 6 Replies View Related

Networking And Gateways :: What Is Meaning For Port Number In Listener.ora File

Aug 12, 2013

what is meaning for port number,in listener.ora file associated with ? is there any difference for port number in windows & in linux server.,?

View 3 Replies View Related

Meaning Of Lines CPU Cores And 99th Percentile In Average Active Session

Apr 18, 2013

I´m monitoring a 11g database with OEM. I have a couple of questions regarding the Average Active Sessions chart:

- What does the line 'CPU core' means? The DB is running in a virtual server with 8 CPUs. However, the line 'CPU core' is in 1. Does it mean that Oracle is just using 1 CPU?
- What represents the '99th percentile' line? The chart shows several sessions above that line, something is not working well, isn't it?

View 4 Replies View Related

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

I have the following query:

select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;

What I want to add is the following:

COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G

I need to connect the NON-NULL rows to the preceding NULL row.

View 15 Replies View Related

SQL & PL/SQL :: Counting NULL Vs NON-NULL In A GROUP BY Clause

Jun 21, 2010

I am running a GROUP BY query on a few columns of enumerated data like:

select count(*), Condition, Size
group by Condition, Size;

COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S

Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.

For example:

COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL

View 9 Replies View Related

SQL & PL/SQL :: Date Field - Not Null Column To NULL

Mar 16, 2011

I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.

I am using below query.

ALTER TABLE T_test
modify (paid_to_date null)

View 9 Replies View Related

PL/SQL :: Analytic Function To List Max

Oct 23, 2012

I have oracle table has records like this clnt pno beg_dt end_dt load_ts

502852     02     01-NOV-93     31-OCT-94     01-AUG-12
502852     01     01-OCT-93     31-AUG-94     01-SEP-12
502866     01 01-JUN-90     31-DEC-90     01-AUG-12
256974     01     01-JAN-90     31-MAY-90     01-SEP-12
280441     01 01-JUN-96     31-MAY-97     01-AUG-12

[Code]....

I am writing an oracle query using analytic funtion to list that has max(load_ts)

But my query returns bad results and retrieve all the records for some reason.

select a.*
from
(
select
CLNT,

[Code]....

View 5 Replies View Related

SQL & PL/SQL :: Create Normal Partition Range On Date And Sub Partition List On Batch ID

Mar 17, 2011

I Know we can create dynamic partitions on table in oracle 11g. Is it possible to create normal partition and sub partition both dynamically.I have to create Normal partition range on date and sub partition list on Batch ID (varchar).

View 3 Replies View Related

Performance Tuning :: Select Partition Table With Non-partition Key Condition?

Jun 26, 2010

I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk

when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)

View 3 Replies View Related

SQL & PL/SQL :: Apply Redefinition And Create Range Partition And Hash Sub-partition?

Apr 3, 2013

At present we have a non partitioned table.

Can we apply redefinition and create range partition and hash sub partition on it?

View 2 Replies View Related

SQL & PL/SQL :: Possible To Make Query With Analytic Function

Nov 4, 2011

Is it possible to make query with analytic function or somehow to represent output data for next example (sql: ???):

Table: Order
sql: select * from order
Sql output>
Item Barcode Qty
---- ------- ---
1 100100 2
2 100200 1
3 100300 3

Table: Order
sql: ???
Sql output>
Item Barcode Qty ElementarQty
---- ------- --- ---
1 100100 2 1
1 100100 2 1
2 100200 1 1
3 100300 3 1
3 100300 3 1
3 100300 3 1

View 2 Replies View Related

SQL & PL/SQL :: How To Write Analytic Function Without Aggregate

Mar 8, 2012

Do we have analytic function equivalent of following?

select object_type,owner from dba_objects group by object_type,owner ;

I am trying to get a unique srno for a combination of a 2 fields - here object_type and owner

OWNEROBJECT_TYPESRNO
SYSVIEW1
SYSTABLE2
SYSPROCEDURE3
SYSTEMVIEW4
SYSTEMTABLE5
SYSTEMFUNCTION6
SYSTEMPROCEDURE7
SCOTTTABLE8
SCOTTVIEW9
.......................

also how can I get the SRNO?

I can' use sequence in the group by function and if I get equivalent analytic for above group by even then I can't write row_number as the order by gives detail record

I don't want to wrap this select inside other select

View 4 Replies View Related

PL/SQL :: Analytic Function Evaluation Order?

Sep 30, 2013

ihave query quite like this: with--

This query selects one

"representant" acct_id per group (about 300 rows total)acct_repres as(  select distinct acct_id, origin_id,  acct_parm_id from  (  select a.*  ,  source_id  , dense_rank() over (partition by source_id origin_id order by acct_nbr nulls first, acct_id) as odr    from account a join account_parm  ap on (a.parm_id = ap.acct_parm_id)  )  where odr = 1)select col1    , col2     , ( select accct_id from acct_repres ar where ar.acct_parm_id = t2.acct_parm_id) col3    , ( select count(1) from acct_repres) col4from some_table t1join other_table t2 on (....) 

And here it comes.

The "acct_repres" subquery returns more than 300 rows when executed separately. But when used in CTE sometimes (depending on execution plan) it seems to have only one row - the value in the column col4 is "1",while value for col3 is NULL for most of the cases. It looks like the the dense_rank function and the condition "where odr =1" are evaluated at the very end.

When I use MATERIALIZE hint the result was the same. But when I put the result of account_repres into dedicated table and use that table instead of CTE the output is correct.

View 6 Replies View Related

SQL & PL/SQL :: Unique Null And Multiple Non-null

Oct 24, 2013

create table test
(
id int ,
dat date
)
/

I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.

create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);

insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);

The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this

iddat
------------
124/10/2013
123/10/2013
123/10/2013
1

because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.

View 2 Replies View Related

SQL & PL/SQL :: How To Modify Null Column To Not Null

Jan 9, 2012

when i follow this steps mention on this website

[URL].........

to modify column from null to not null i got this error and on this website its show successful

my steps are

first i create a table

SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));

Table created.

Then insert some rows into Stu_Table

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');

1 row created.

SQL> select * from Stu_Table;

ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh

Table Structure is like this

SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)

now when i try to modify this Stu_id column to not null its give me error.

SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

and when i try to add new column with not null its also gives me error

SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

View 6 Replies View Related

SQL & PL/SQL :: How To Refer Result Of Inner Query (analytic Function) In Outer One

Feb 23, 2012

How can I refer the result of inner query - max(t.col3) in the outer query. I tried the 'group by' but it is not performing well

select
t1.col5, t1.col6
from t1,
(select t.col1
t.col2
[code].......

View 8 Replies View Related







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