All Columns Of A Table Are Part Of Primary Key Column Of That Table?

Feb 22, 2011

if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).

should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.

there is a drawback if a most of the tables of a database contain composite primary keys?

View 3 Replies


PL/SQL :: Find Out Column / Combination Of Columns From Given Table

Sep 23, 2013

Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.

View 10 Replies View Related

SQL & PL/SQL :: Update Column In A Table Which Has 3 Columns Of 16 Million Rows?

Apr 25, 2012

trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.

Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.

Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.

View 13 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv


View 2 Replies View Related

SQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child

Aug 13, 2012

We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,

Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z

Requirement :

From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this

-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z

Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.

SQL Statements to create hierarchy table:

create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');

Approached already tried :

1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;

2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);

Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

i_e NUMBER(10);
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)


View 9 Replies View Related

Server Utilities :: Import A Table With Table Already Present With New Columns

Mar 31, 2010

I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.

The new table
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------

It giving error while doing an import.

View 4 Replies View Related

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

Performance Tuning :: Update Columns Of One Table Using Another Table

Feb 6, 2011

I am trying to update columns of Table A with the columns of Table B. Both these tables have 60,000 rows each. I tried this operation using following 2 queries:

Query 1

Update TableA A
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)

Query 2
Update TableA A
(A.col1,A.col2,A.col3)=(select B.col1,B.col2,B.col3
from TableB
where A.CODE=B.CODE)
where exists
A.code = (select B.code
from TableB B
where A.code=B.code)

When i execute these two above queries, it keeps executing indefinitely.

View 4 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I have a two question.

Question 1:How to select all columns from table except those columns which i type in query

Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data

View 5 Replies View Related

Application Express :: Get Part Of URL As Part Of Dynamic Action When Clicking A Button?

Nov 3, 2013

I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?

View 6 Replies View Related

SQL & PL/SQL :: Extract Only Number Part Of Column?

Mar 25, 2010

In a table, I have a column in a table with the following values:


30 WD
180 WD
180 CD
7 WD
2 WD


where WD is working days and CD Calendar days.

I just need to extract number from above.

View 11 Replies View Related

Finding Primary Key In A Table?

Oct 4, 2007

Whats the command to show the name of the primary key in a table using oracle sql plus?

View 1 Replies View Related

SQL & PL/SQL :: Table Partitioning With No Primary Key

Jan 24, 2013

we apply partitioning concept on a table which don't have any primary key ?

I just want to add one more field as primary key with some sequence generated values while partitioning ? Is it possible ?

View 7 Replies View Related

Text :: Make Part After AND Not Match Already Matched First Part?

Nov 2, 2012

I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.

The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:

Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;

But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?

View 2 Replies View Related

Add Partial Primary Key Into Oracle Table?

Oct 17, 2006

how to add a primary key into the oracle table but how do i add a partial primary key?

View 2 Replies View Related

SQL & PL/SQL :: Audit Table With Primary Keys?

Feb 19, 2013

I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.

CREATE TABLE emp AS (SELECT * FROM scott.emp);
CREATE TABLE audit_table


View 10 Replies View Related

SQL & PL/SQL :: Update Table With Primary Key In Where Clause

Dec 11, 2012

Is is required to check the number of rows updated in a table when the primary key of the table is used in the filter criteria of the update statement? As I know,by default it will update only one record. But if it happens to be an important transaction table and only one record is required to be updated, then is it the best practice to use the 'SQL%ROWCOUNT' check in the query, even if the update query is using primary key in filter clause.

Example:Consider Trans table with trans_id as primary key. Then:
Update Trans
set trans_status='pass'
where trans_id=123;

I know this will update only one record. But what is the best practice? Shall I use 'SQL%ROWCOUNT' after this update to double check whether the record is updated or not?

View 7 Replies View Related

SQL & PL/SQL :: Primary Key On Table Having Some Duplicate Record?

Jul 14, 2010

Is it possible to apply primary key on table having some duplicate record?I can do this by deleting duplicate record, But I don't want to delete exisitng data.

View 10 Replies View Related

How To Reorganize A Table In-order Of Primary Key

Jul 13, 2010

I would like to reorganize a table inorder of primary key but I'm not sure if I'm expecting the right thing from dbms_redefinition package.

I am working on oracle 9i

I have the following table :

---------- ----------
1 FF
2 BB
3 CC
4 DD
8 EE
6 ZZ
7 YY
5 GG

when I use the CODEexec dbms_redefinition.start_redef_table('SCOTT', 'TESTTABLE', 'TESTTABLE2', 'id id, ref ref', dbms_redefinition.cons_use_pk);

Would the newly created scott.testtable be created in order of primary key (ID) thus a select * from scott.testtable will give me an ordered result?

When I do the test, the table before and after the redefinition is exactly the same so why use the CONS_USE_PK if it doesn't order the table by primary key?

View 5 Replies View Related

Performance Tuning :: Table Partitioned With Primary Key

Aug 4, 2010

I have normal tables with hugh Data and would like to increase the performace by following means:

1) Add a new column in each table. Say this column Name is IS_LIVE. This new column have only two value 1 ( LIVE ) OR 0 ( NOT LIVE ).
2) Change the normal tables to Partitioned table. There would be only two partitioned in all the table. The partitioned key column would be IS_LIVE and both partitioend recrods would be in two different tablespace.
3) Added a POLICY function to these partitioned table to Always add a Query Predicate of '1' to all queuries.

I am interested to know that what kind of Indexes ( Global Or local ) would be suitable for these kind of Design.Is there any use of having Local index on IS_LIVE.Please note that Primary Key doesnot have this new column in it.

View 10 Replies View Related

SQL & PL/SQL :: Add Composite Primary Key To Table That Has Self Referencing Foreign Key

Jan 21, 2012

I have an employee table which has a primary key and a self referencing foreign key, as shown here

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Now if i make the primary key composite, as shown below -

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id, name)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Oracle is throwing the following error -

ORA-02270: no matching unique or primary key for this column-list

How can this error be fixed without changing the composite primary key?

View 10 Replies View Related

SQL & PL/SQL :: How To Have Procedure Populate Staging Table Primary Key

Jun 6, 2012

I've a staging table STG_TABLEA which has a primary key discount_seq_no.

I am creating a pl/sql procedure to populate a primary key (discount_seq_no) with a database sequence. The intent is to keep this populated with next value incrementing by 1.

I am using Oracle version.

I've put together the below code, not sure on next steps...


View 5 Replies View Related

SQL & PL/SQL :: Create Primary Key Using Local On Partitioned Table?

Mar 2, 2012

I have to create primary key using local on a partitioned table.

Since the table is huge it has to be done parallel. Following stmt is giving error


View 5 Replies View Related

SQL & PL/SQL :: ORA-12014 / Table CDA_FUNCTION Does Not Contain A Primary Key Constraint

Dec 9, 2010

I am trying to create snapshot using the following script but I am getting the error

ERROR at line 22:ORA-12014: table 'CDA_FUNCTION' does not contain a primary key constraint



I check in dba_constraint data dictionary table for the constraints in the table is showing as follows

owner constraint_name constraint_type table_name search_condition status deferrable deferred validated generated

View 4 Replies View Related

PL/SQL :: Data Dictionary To See Foreign And Primary Key Of Table

Feb 17, 2013

which data dictionary should i choose to see the foreign key and primary key of table

View 2 Replies View Related

Server Administration :: Enabling Primary Key On A Big Table

Jun 22, 2012

I have a partioned table that has close to 2 billion rows and a PK of all columns. Becuase of time constrains my APP team wants the PK disabled while they pump into hundreds of thousands of rows with a batch process.

Now I am finding when I enable the PK its eating up close to close to 200GB of temp space.

Is there something I can do to reduce the amount of temp space being used?

View 6 Replies View Related

Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?

May 11, 2012

I am trying to update currency column of one table using the currency column of other table using the following sql code.

set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)

This update is taking taking a lot of time and is never ending.

should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?

View 7 Replies View Related

SQL & PL/SQL :: Create Normal Primary Constraint During Table Creation

Mar 23, 2010

i'm new to oracle can i specify NONCLUSTERD INDEX on Primary cloumn during table creation.By default it will create clusterd index but i need non-clusterd index on it.

I'm using following stmt to create normal primary constarint during table creation,


how can i change the above query, so that it should create NONCLUSTERED INDEX on Primary key column.

View 5 Replies View Related

SQL & PL/SQL :: How To Copy Constraints (primary / Foreign Key / Indexes) To A Table

Mar 11, 2011

I have created table as below

create table emp_temp as select * from emp;

the table is created, but the constraints are not copied. Is there any way to copy all the constraints.

View 3 Replies View Related

Copyrights 2005-15, All rights reserved