concept of composite and candidate keys. i have gone through all the definition part but did not understand the concept of creating candidate key for example:-
composite key
create table test(id number,pincode number(4),name varchar2(15),primary key(id,pincode));
table created with composite bcz i took pair of id and pincode.
here base on the same example want to create candidate key with the syntax.
I am trying to install 2-node 11gR2 RAC on CentOS 5.6 using VMWare Fusion. I'm in the middle of Grid Infrastructure installation and while choosing the ASM diskgroups I don't see any candidate disks but when I click on all disks i can see them but can't select them.
I tried installing the grid before and ended up with and issue running the root.sh on all the nodes so had to deconfigure the grid binaries using the rootcrs.pl and then deinstalled the grid software using the ./desintall utility.
when tried installing again ...I ran the cluvfy utility ..it doesn't show any errors.
We're trying to utilize Oracle RAC 11.2. We want to have two servers in our grid. These two server are installed on a VM using Hyper-V software.
common storage for asm is also developed on a VM using FreeNAS software.Now, when we try to install Grid Infrastructure the disk that is recognized as a extra drive in both servers are disabled and we can't select it as ASM storage. The OS we're using is Windows Server 2008 R2.I have to say the IP requirements are considered and the servers have two IPs.
understanding composite primary key.
I know what is composite key and how it stores the data.. but i am not able to find when i have to choose the columns to be composite keys.
I have the following 3 columns in my VERSIONS table.
MAJOR
MINOR
MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions
select max(minor) as max_minor from versions where major = :max_major
select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
I have the following case:
create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)
[Code]....
I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used
select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;
I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.
I have a lot of queries on a table with WHERE clause:
WHERE CR_DATE > :y AND CR_VALUE = :x
Actually, there is an index on (CR_DATE) but much more selective index is on (CR_DATE, CR_VALUE). If I do not UPDATE any records on this table, is there any difference in INSERT operation (or another problem) when I replace actual index with multi-column index?
problem with populating the collection object.
I am getting an error -
'ORA-06530: Reference to uninitialized composite"
My Code is as below
create type test_t as object (empno number, ename varchar2(20))
/
create type test_tt as table of test_t
/
create or replace procedure test_p
is
[code]....
I am following Lynda Tutorial for Normalization. I have understand normalization.The core concept of primary key is that it uniquely identifies each record in the table, but here in the given below image the 'COURSE' field is repeating 'SQL101' value again and again but still the teacher in the video is calling it primary key and combination of date & Course column Composite key.how can a field which vales are repeating can be called as Primary Key?
View 12 Replies View RelatedI am replacing a composite PK with a new single PK as business changed. I am creating sequence to fill the new field that should present the PK but I need to fill it according to the values of the old composite PK.
CREATE TABLE ABC
(
COMP_PK1 NUMBER,
COMP_PK2 NUMBER,
COMP_PK3 VARCHAR2(8),
VAL VARCHAR2(50),
PRIMARY KEY (COMP_PK1, COMP_PK2,COMP_PK3)
);
[code].....
I need to update ABC and set ABC_SERIAL = SEQ_ABC.nextval but i need this to be done according to the order of the old composite primary key... i.e. rows with COMP_PK1 = 1 are filled before rows with COMP_PK1 = 2 and so on..
I have to create six tables as part of my assignment. I have created 5 tables. I am having trouble with the 6th. How can I reference a foreign key to a composite primary key? I have colored red the areas that I think are giving me trouble which is in the 6th table, the Registration Table.
Create Table Student
(
StudentID number (6,0) NOT NULL,
Student_Name varchar2 (20) NOT NULL,
Constraint Student_PK Primary Key (StudentID)
);
Create Table Faculty
(
FacultyID number (5,0) NOT NULL,
Faculty_Name varchar2 (20) NOT NULL,
Constraint Faculty_PK Primary Key (FacultyID)
);
Create Table Course
(
CourseID varchar2 (10) NOT NULL,
Course_Name varchar2 (20) NOT NULL,
Constraint Course_PK Primary Key (CourseID)
);
Create Table Qualified
(
FacultyID number (5,0) NOT NULL,
CourseID varchar2 (10) NOT NULL,
Date_Qualified varchar2 (10) NOT NULL,
Constraint Qualified_PK Primary Key (FacultyID, CourseID),
Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
);
Create Table Section
(
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
CourseID varchar2 (12) NOT NULL,
Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
);
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
);
This is the error I am receiving:
Error starting at line 1 in command:
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
)
Error at Command Line:8 Column:104
Error report:
SQL Error: ORA-02256: number of referencing columns must match referenced columns
02256. 00000 - "number of referencing columns must match referenced columns"
*Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
*Action: Make sure that the referencing columns match the referenced columns.
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?
How can i apply composite key on a table?
View 2 Replies View RelatedI have a table say MY_TAB with columns as below
emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...
[URL]......
Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?
I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?
11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;
Table dropped.
Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....
- dynamic sampling used for this statement
22 rows selected.
I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,
[code]...
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
