Google+

Saturday, August 14, 2010

NOCYCLE sequence

SQL> -- A NOCYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
  2 INCREMENT BY 50000
  3 START WITH 50000
  4 MAXVALUE 99999
  5 NOCACHE
  6 NOCYCLE;

Sequence created.

SQL>
SQL>
SQL> select studentNumSeq.nextVal from dual;

  NEXTVAL
----------
  50000

SQL> select studentNumSeq.nextVal from dual;
select studentNumSeq.nextVal from dual
  *
ERROR at line 1:
ORA-08004: sequence STUDENTNUMSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated


SQL> select studentNumSeq.nextVal from dual;
select studentNumSeq.nextVal from dual
*
ERROR at line 1:
ORA-08004: sequence STUDENTNUMSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated


SQL>
SQL>
SQL> drop sequence studentNumSeq;

Sequence dropped.


Note:-Please comment and reply me.

Creating a trigger based on two tables

SQL>
SQL>
SQL> CREATE TABLE myTable1 (a INTEGER, b CHAR(10));

Table created.

SQL>
SQL> CREATE TABLE myTable2 (c CHAR(10), d INTEGER);

Table created.

SQL>
SQL>
SQL> CREATE TRIGGER trig1
  2 AFTER INSERT ON myTable1
  3 REFERENCING NEW AS newRow
  4 FOR EACH ROW
  5 WHEN (newRow.a <= 10)
  6 BEGIN
  7 INSERT INTO myTable2 VALUES(:newRow.b, :newRow.a);
  8 END trig1;
  9

Trigger created.

SQL>
SQL> insert into myTable1 values(1,'a');

1 row created.

SQL> insert into myTable1 values(2,'b');

1 row created.

SQL>
SQL> select * from myTable1;

  A B
---------- ----------
  1 a
  2 b

SQL> select * from myTable2;

C D
---------- ----------
a 1
b 2

SQL>
SQL> drop table myTable1;

Table dropped.

SQL> drop table myTable2;

Table dropped.

Note:-Please comment and reply me.

Sunday, August 8, 2010

Sequence cache option

Sequence cache option 


SQL> -- the cache option specifies how many sequence values will be stored in memory for
faster access
SQL>
SQL>
SQL> CREATE SEQUENCE mySequence
  2 MINVALUE 1
  3 MAXVALUE 999999999999999999999999999
  4 START WITH 1
  5 INCREMENT BY 1
  6 CACHE 20;

Sequence created.

SQL>
SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  1

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  2

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  3


Note:-Please comment and reply me.

Alter sequence increment

 Alter sequence increment

SQL> CREATE SEQUENCE mySequence
  2 MINVALUE 1
  3 MAXVALUE 999999999999999999999999999
  4 START WITH 1
  5 INCREMENT BY 1
  6 CACHE 20;

Sequence created.

SQL>
SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  1

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  2

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  3

SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  4

SQL>
SQL> alter sequence mySequence
  2 increment by 124;

Sequence altered.

SQL>
SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  128

SQL>
SQL> alter sequence mySequence
  2 increment by 1;

Sequence altered.

SQL>
SQL> select mySequence.nextval from dual;

  NEXTVAL
----------
  129

SQL>
SQL>
SQL> drop sequence mySequence;

Sequence dropped.

SQL>
SQL>


Note:-Please comment and reply me.

Friday, August 6, 2010

Sequence Example 3

 Create sequence emps_seq start with 501 increment by 10

SQL>
SQL> create sequence emps_seq
  2 start with 501
  3 increment by 10;

Sequence created.

SQL>
SQL> drop sequence emps_seq;

Sequence dropped.

SQL>
SQL>


Note:-Please comment and reply me.

Sequence Example 1


SQL> CREATE SEQUENCE supplier_seq
  2 MINVALUE 1
  3 MAXVALUE 999999999999999999999999999
  4 START WITH 1
  5 INCREMENT BY 1
  6 CACHE 20;

Sequence created.

SQL>
SQL> select supplier_seq.nextval from dual;

  NEXTVAL
----------
  1

SQL> select supplier_seq.nextval from dual;

  NEXTVAL
----------
  2

SQL> select supplier_seq.nextval from dual;

  NEXTVAL
----------
  3

SQL> select supplier_seq.nextval from dual;

  NEXTVAL
----------
  4

SQL>
SQL> drop sequence supplier_seq;

Sequence dropped.



Note:- Please comment and reply me.

Sequence Example 2


SQL> create sequence deptno_seq
  2 start with 50 increment by 10;

Sequence created.

SQL>
SQL> select deptno_seq.nextval, deptno_seq.currval
  2 from dual;

  NEXTVAL CURRVAL
---------- ----------
  50 50

SQL>
SQL> select deptno_seq.currval
  2 from dual;

  CURRVAL
----------
  50

SQL>
SQL> select deptno_seq.currval, deptno_seq.nextval
  2 from dual;

  CURRVAL NEXTVAL
---------- ----------
  60 60

SQL>
SQL>
SQL> drop sequence deptno_seq;

Sequence dropped.



Note:-Please comment and reply me.

If id is null, use the value from sequence

If id is null, use the value from sequence
 
SQL> create table myTable(
  2 id number primary key,
  3 blob_content blob
  4 )
  5 /

Table created.

SQL>
SQL> create sequence myTable_seq
  2 /

Sequence created.

SQL>
SQL> create or replace trigger biu_myTable
  2 before insert or update on myTable
  3 for each row
  4 begin
  5 if :new.id is null then
  6 select myTable_seq.nextval into :new.id from dual;
  7 end if;
  8 end;
  9 /

Trigger created.

SQL> drop table myTable;

Table dropped.

SQL> drop sequence myTable_seq;

Sequence dropped.




Note:-Please comment and reply me.

Wednesday, August 4, 2010

Hardware CPU Guide Part I: Factors That Affects a CPU’s Performance

 Being the brain of the computer, the CPU plays a very important role in determining the performance of the system. Unfortunately, when it comes to choosing the best CPU, you will probably feel like a lost sheep. With different brands, models, speeds and specifications to choose from, it can really be a difficult task to decide which CPU is the right one for you.
In this three part guide, we will give you a good overview of “the factors that affect a CPU’s performance”, the differences between an Intel and AMD CPU and how you should go about choosing the the CPU that is best suited to your needs.
This is the first part of the CPU guide.

What is a CPU?

The CPU (Central Processing Unit), or sometimes known as processor, is one of the most important component in a computer system. Being the brain of the computer system, its task is to take care of all the data calculation and make sure they are processed in the fastest time possible.
CPU is not something you can see from the outside of the computer. In fact, you won’t be able to see the CPU on a fully-assembled PC. To see it, you have to remove the computer casing, unplug the wire and remove the heatsink (and fan), only then can you see the surface of the CPU. The shape of the CPU is a small square chip with lot of connector pin underneath.
The images below show the back and the top of a CPU.


How CPU works

To keep it simple, the way a CPU works can be illustrated with the following 3 steps:
  1. When you click to execute an application, the raw instruction is first fetched from the hard disk (sometimes from the memory) and sent to the CPU for processing.
  2. When the CPU receives the instruction, it will execute the logic and compute the result.
  3. Once the CPU finishes processing, it will send the result to the respective device to output to the user.
While it may seems easy, all these 3 steps must be completed in split seconds. Delay in any of these steps will result in a lag in the computer.

Factors that affect a CPU performance

It is easy to think that the speed of the CPU is directly link to the performance of the CPU. This is only true to a certain extent. A CPU with fast speed will not be efficient if it has only a limited data to process. To achieve maximum efficiency, the hardware (especially the hard drive and memory) that are linked to the CPU must supply data as fast as the CPU speed. Failure to do this will result in a lagging computer, regardless how fast the CPU is.

1. CPU Clock Speed

The operating frequency of the CPU (also known as the clock speed) determines how fast it can process instruction.

The speed is measured in terms of Hertz, and it is usually lies in the megaHertz (MHz) or gigaHertz (GHz) range. A megaHertz means that the CPU can process one million instruction per second whereas a gigahertz CPU has the capability to process one billion instructions per second. In today technology, all CPUs run in the gigahertz range and you seldom see CPU with speed in the MHz range anymore.
Theoretically, a 500 MHz CPU is six times slower than a 3 GHz CPU and a 3.6 GHz CPU is faster than a 3 GHz or a 3.4 GHz CPU. In general, the higher the frequency of a CPU, the faster the speed of the computer.

2. Cache

Remember we mentioned above that for the CPU to work at its maximum efficiency, the data transfer from the other hardware must be as fast as its speed. The purpose of a cache is to ensure this smooth and fast transition of data transfer from the hardware to the CPU.
To understand the importance of a cache, it is necessary to understand how the whole process works. The main bulk of information comes from the hard drive. When an application is requested, the motherboard will fetch the required information from the hard drive and deliver it to the CPU for processing.
Since the hard drive processing speed is much slower than the CPU, data transfer often takes a long time. To speed thing up, the RAM is used to store temporary information from the hard drive. Instead of heading straight to the hard drive, the motherboard now checks and retrieves the data from the RAM. Only when the required information is not found in the RAM then will the motherboard go to the hard drive.
As CPU speed increased to the point where the RAM is no longer able to catch up, the transferring of information again become a serious problem. To solve this issue, a cache, which was effectively a small and extremely fast memory, was added to the processor to store immediate instruction from the RAM. Since the cache runs at the same speed of the CPU, it can rapidly provide information to the CPU at the shortest time without any lag.
cpu-cache-system
There are different levels of cache. Level 1 (L1) cache is the most basic form of cache and is found on every processor. Level 2 (L2) cache has a bigger memory size and is used to store more immediate instructions. In general, the L1 cache caches the L2 cache which in turn caches the RAM which in turn caches the hard disk data. With the newer multi-core technology, there is even a L3 cache that is bigger in size and is shared among the various cores.
L2/L3 cache plays the greatest part in improving the performance of the processors. The larger the cache size, the faster the data transfer and the better the CPU performance. However, cache is very costly. That is why you don’t find 1GB of cache in your system. The typical cache size is between 512KB to 8MB. The latest Intel Core i7 Extreme Processor comes with a 12MB L3 cache, which also explains its hefty price tag of approx. $1,000.

3. Multi-Core

In the past, if you want to get a faster computer, you have to get a faster CPU. Today, this is only partially true. The reason being, CPU speed can’t increase forever. There is limitation as to how fast the transistors can run. When it reaches a plateau, you won’t be able to increase the speed anymore.
The speed is measured in terms of Hertz, and it is usually lies in the megaHertz (MHz) or gigaHertz (GHz) range. A megaHertz means that the CPU can process one million instruction per second whereas a gigahertz CPU has the capability to process one billion instructions per second. In today technology, all CPUs run in the gigahertz range and you seldom see CPU with speed in the MHz range anymore.
Theoretically, a 500 MHz CPU is six times slower than a 3 GHz CPU and a 3.6 GHz CPU is faster than a 3 GHz or a 3.4 GHz CPU. In general, the higher the frequency of a CPU, the faster the speed of the computer.



Note:- Please comment  and reply me.