Skip to main content

Database usage in development and testing environment


In development environment we have access to the database in the read and write mode, where we can play with tables. Instead of writing simple queries to update any tuple, we use for update functionality of the PL/SQL. But it is a bad idea, since we are blocking other users who are operating on the same table, since for update table the lock on the full table, and noone is allowed to perform any operation untill the lock is released.

select * from table_name for update;

Instead of the above query write a simple update query.

update table_name set column_to_set = value_to_set
where column_where_condition = value_where_condition;

This is better idea to practice, or you can simple create a query as mentioned below :

update &table_name set &to_set_column_name = &value_to_set_column_name
where &where_column_name = &value_where_column_name;


This will help the user to input all the details at runtime.

But still in the testing environment, when the developers don't have access to the tables. They are not able to describe the table in the traditional way. so, they can use some other queries which are provided by oracle.

Say, for instance, if you want to describe a table, you can use the following query :

SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name='TABLE_NAME';

Here, TABLE_NAME is case sensetive and always in caps.

Other table queries are also available, like :

SELECT *
FROM user_col_comments
WHERE table_name='TABLE_NAME';

Labels:

Comments

Post a Comment

Popular posts from this blog

Nested Loop Natural Join

When the optimizer tries to finalize the execution plan of a query, it considers a lot of items. It must take the interrelated decisions based on those items. Most important of those items are, Ø   Access path Ø   Join Order Ø   Join Operation Access path tells how the required data is going to be retrieved from a table. So, this tells nothing but which index scan is imposed on that table like index range, index skip scan and so on. Join Order means, to execute a query that joins more than two tables, Oracle joins two of the tables, and then joins the resulting row source to the next table. This process is continued until all tables are joined into the result. It means oracle can join only two tables at most in a time though more than two tables are referred in a query. Oracle always tries to join the small tables first and then joins with the large tables. The reason behind is, it always tries to lower the number of resultant records formed while in the process of jo...
Post a Comment
Read more

Bitmap index

Bitmap index is one of the index types that is supported by oracle. Unlike B*tree index, this is very compressed index. It means if I create a bitmap index on a column, then the generated index table will be smaller when compared the index table which is generated by the binary index on the same column. The reason is, in bitmap index, ROWIDs won’t be stored in the index table, instead index values will be mapped against bitmaps. Bitmap is nothing but it stores either of this values, 1(Match) or 0(No Match). For each distinct values in the column, a separate index record will be created. Unlike b*tree index, bitmap index can accept NULL and it creates separate index entry for this. Bitmap index will work better if number of distinct values are relatively less when compared to the total number of records. Why is it referred as “relatively less”? this index will work fine if table of 1,000,000,000 records contains 5000 distinct values and if table of 1,000 records contains 5 distinct va...
Post a Comment
Read more

IOT – Index Organized Table

Oracle supports these 3 different types of tables, 1.      Heap Table (nothing but Data Table) 2.      IOT 3.      Cluster Table (using Cluster Index) First type, heap table is everyone aware of. Heap Table is of rows and columns and value is stored in each cells (each cell è intersection of each rows and columns). In Oracle, all these tables containing data are stored in the datafiles which would be in HardDisk. In datafile, fundamental storage unit is block (in other databases, this might be “Pages”). Logical representation of the datafile, the block, the data will be like this, “ROWID” is the memory address assigned to the each record getting stored in a block of a datafile. ROWID can be decoded to get the actual block number and the datafile name on which the particular record is stored. . If Suppose say, datafile is of 3MB. If size of single block is say 1MB, then 3 blocks would be in this single data file. Conside...
Post a Comment
Read more