This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
A user named user5716448 is using Oracle version 18.104.22.168. He has a large fact table and is doing some comparitive tests on loading large amounts of data which is several hundred GB into a fact table range partitioned on date. He thinks that if using partition exchange the loading will be much faster to load a partitioned table but he want to know why it takes a standard sql insert much longer to load a partitioned table as compared to a non-partitioned table. The partitioned table has local partitioned bitmap indexes as compare to the non-partitioned table which has stand non-partitioned bitmap indexes.
A user "Iordan Iotzov" mentioned a few reasons for this type of behavior. When no indexes are involved, the table partitioning will force the data to scattered across multiple segments and will lead to different retrieval techniques compare with a "regular" full table scan. When indexes are involved, local partitioned indexes can cause an all-partition index scan if the partition key is not specified in the query.
Another user "Hemany K Chitale" suggested that he needs to identify the queries first. For example, is "count(*)" a regularly used query? For rows without NULLS, change to something like" count(bitmap_indexed_column_that_is_NOTNULL)". For all rows including nulls, change to "count(bitmap_indexed_column)".
(a) Oracle Database uses two ways to plan capacity for database objects, with enterprise manager and with the DBMS_SPACE PL/SQL package. A database table's size depends on many factors like the tablespace storage attributes, block size and others. With the DBMS_SPACE package, the CREATE_TABLE_COST procedure allows user to estimate the space usage to create a table. The procedure consists of two variants. Firstly, it uses average row size to estimate size and secondly, it uses column information to estimate table size.
With the DBMS_SPACE package, the CREATE_INDEX_COST procedure allows user to estimate the space usage when create an index on an existing table.
The procedure OBJECT_GROWTH_TREND of the DBMS_SPACE package will produces a table of one or more rows and each row will describe the space usage of the object. The procedure will get the total space use from the Automatic Workload Repository or generate the current space usage and merge with the historic space usage changes retrieved from the Automatic Workload Repository.
(b) Oracle database has a variety of indexing schemes which helps to give a complementary performance functionality. The indexes include B-tree indexes, bitmap and bitmap join indexes, function-based indexes and application domain indexes. B-Trees also stands for balanced trees. It is an ordered list of values split into ranges. By connecting a key with a row or many rows, it can give very good retrieval performance.
Oracle provides an index scan, the database will traverse the index and retrieve a row, while using indexed column values specified by the statement. Once the database scans the index for a value, it will locate this value in n I/Os where n is the height of the B-tree index. Oracle provides a full index scan where the database will read the entire index in order. If data is sorted in ordered by the index key, a full scan can eliminate sorting. Oracle provides a fast full index scan where the database will access the data in the index directly without the need to access the table first. An index range scan is an ordered scan of an index which have two unique characteristics(one or more columns of an index are specified in conditions) and index key have possible values of 0,1 or more. Oracle also provides Index Unique scan and Index Skip Scan.
The index clustering factors measures the row order in relation to an indexed value. If the clustering factor is high, Oracle database will perform a somewhat large number of I/Os during a large index range scan. If the clustering factor is low, Oracle database will perform a somewhat small number of I/Os during a large index range scan. Clustering factor is important for index scans as it shows whether the database will use an index for large range scans, the degree of table organization in relation to the index key and whether rows must be ordered by the index key.
In an ascending index, Oracle database will store data in ascending order. Whereas in descending index, Oracle database will store data in descending order.
In a key compression, Oracle Database can use it to compress portions of the values of a Primary Key in either a B-tree index or index-organized table. It can help to save the space consumed by the index.
In a bitmap index, the Oracle Database will store a bitmap for each index key. For each indexed key, it will use a B-tree index structure to store the bitmaps.
Oracle database stores index data in an index segment. Every segment contains the data for a bitmap index. Oracle Database stores it by one or a lot pieces. Each pieces contains part of single data block.
(c) Oracle Database has a software product that will create screen to interact with. It is called the Oracle Forms. Oracle Forms will interact with the Oracle Database and then create a screen to present the data. The form is used to view and edit data in database driven application. With the Oracle Forms, some GUI elements like buttons, scrollbars, menus and even graphics can be placed on the form. It also has built-in record to create query and update modes. This function eliminates the needs to create dynamic SQL, locking rows and others.
It is now possible for outside events to trigger internal Forms code using the Oracle Forms's Advanced Queuing functionality. Now other forms applications or third party applications can post events through Advanced Queuing and through the database package DBMS_AQ.
The Forms server will know when a problem arise by the event raised by Advanced Queuing as Forms uses HTTP protocol, which is a request and response protocol. A new application property called MAX_EVENT_WAIT has been introduced to allow developers to specify how long the delay is between when an event is raised and when its effects are propagated.
(d) Oracle Database introduced a several encryption schemes to help deciphering the contents. Oracle introduced the DES and hashing. Hashing is commonly used for checking summing methods where hashed checksums are compared before and after the transit to ensure it is not tampered. Most popular is DES(Digital Encryption Standard) which uses a 64 bit key to encrypt data.
Oracle has their own built-in tools. This tools help to implement encryption inside the database. The DES3 encryption is used in many situation to help protect data. The procedure of DES3Encrypt will take in the string to be encrypted and the encryption key. It will decrypt the data and then use the same key to encrypt it again. For example if a key of 16 characters is used, it will pass an 8 bit key to the encrypted data. So the procedure requires a 16-bit key for encryption and if a lesser length key is specified, it will return an error.
A major component of Oracle Advanced Security, the Transparent Data Encryption(TDE). TDE will encrypt data before it is written to storage. It will auto decrypt data when reading it from the storage without making any changes to the application. TDE has two modes, tablespace encryption and column encryption. In Oracle Database, the tablespace encryption provides an effective solution to encrypt the whole application tables. TDE column encryption provides an effective solution to encrypt individual data elements like credit card and social security numbers.
Oracle database provides a built-in two-tier key management architecture that have two keys, the master encryption key and data encryption keys. The master encryption key is used to encrypt and protect the data encryption keys.
Oracle Database security offers a standards-based network encryption to protect the communication to and from the Oracle Database. When encryption is turned off, clients can reject the connections and no changes needed to be make for the applications which allows business to easily deploy network encryption.
(e) Oracle provides a better manageability in handling images. With Oracle Database, images stored in the database can be directly linked with metadata. An image can be manipulated during a transaction and a thumbnail of the image will be created and all associated metadata is modified. When an image is stored to a file system, there is a possibilities that an external process cause the image to be deleted of modify. Another problem is when web quality images loses their associated thumbnails, the web page displays will become broken. With Oracle interMedia, it has more control over the images, allowing images to be manipulated when the images are stored inside the database. They can be modify which allows more simple management of the images.
In Oracle Database, a fine grained security is possible when storing an image. An restriction is placed so that only individual users is able to access the image. A timeout is attached when accessing the image in the database. There is a check in and checkout function. Audit who accessed the image and when. Only one user can access the image for a period of time which others cannot access.
Oracle Database has better extensibility when storing image. An image stored inside the database can be indexed. An image that is a document it can be searched and extracted from it. An image is able to be converted to other format. And it can be modify, copied, resized and control the image quality.
Oracle Database has a good flexibility when it comes to storing an image. Images can be deleted, updated or copied easily just like writing a query. Metadata can be easily attached to images and be linked together.
A(4) Problems may arise when using Multiversion concurrency control. First issue is the timestamp resolution. When the resolution of the timestamp is too huge, there is higher chance that two or more timestamps will be the same thus giving problem to the transactions. For example, a system creating few hundred unique timestamps per second and so there is a chance two events will have the same timestamps as given that two events occur at 2 milliseconds apart.
Another problem is the Timestamp Locking. Although using time-based concurrency control the object is not locked from concurrent access for the duration of a transaction, each timestamp against the object will still need a short period of lock on it during recording of the Object.
Oracle provides Multiversion Concurrency Control by introducing two read consistency to query called the statement-level read consistency and transaction-level read consistency. It also has a read consistency with real application clusters and Oracle provides different isolation levels for the needs of different application and workload.
(a)ODBC stands for Open Database Connectivity. The goal of ODBC is to allow any application to access any data regardless of any Database Management System(DBMS) that is handling the data. ODBC will insert a middle layer between the DBMS and application. This middle layer is also called the ODBC driver. The ODBC has a driver manager which will link the application to it. The application will use the ODBC functions and the ODBC driver will pass the query to the DBMS.
JDBC stands for Java Database Connectivity and it is a Java-based data access technology from Oracle. It is an Application Programming Language for the Java Programming Language which specifies how a client can access database. This technology has various methods to query and update data in a database.
(b) OLAP stands for online analytical processing in computing term. It enables user to view data from different points of view easily. OLAP stores its data in a multidimensional database. OLAP can be used to data mining or to discover relationships between old data items.
OLTP stands for online transaction processing. It is a type of program that make it possible to manage transaction-oriented applications, usually for data entry and to retrieve transactions in a lot of different industries.
(c) PHP is a recursive acronym for PHP: Hypertext Preprocessor. It is a commonly used open source general-purpose scripting language that is suitable for web development and it can be used and embedded into HTML.
(d) LAMP is a collection of solution of open source free software. The acronym LAMP refers to the first letters of Linux, MySQL, Apache HTTP Server, PHP, Python or Perl, principal components to create a practical and feasible standard purpose web server.
(e) BLOB stands for Binary Large Object. It is a large object data type in the database system. It is used to store huge data or document or audio or even video files. BLOB fields only allocate space when the content is utilized.
(f) CMS stands for content management system. It is a computer program to publish, edit and modify content in addition to maintaining from a central interface. It provides procedures to control workflow within a collaborative environment.
(g) CSV stands for Comma-separated values. It is a tabular data that is saved as plaintext data separated by commas. For example :
The CSV Data will be :
(h) NAS stands for Network-attached storage. It is a file-level computer data storage connected to a computer network providing data access to a group of clients. The main difference of a direct-attached storage(DAS) and NAS is that DAS is just an extension for a existing server and may not be networked.
(i) DBA stands for Database Administrator. They are the people responsible for managing the database. They are to perform various database manipulation tasks like archiving, running, testing, security control and others related to the environmental side of the database.
(j) RAID stands for redundant array of independent disks. It is a system developed to combine a multiple disk drive components to form a single storage device with better performance and storage capacity.
B(2) Dealing With Time
(a) When a column in MYSQL table is defined as "timestamp", we should interpret the time and date as "YYYY-MM-DD HH.MM.SS". The different between Timestamp and Datetime is that the supported range is different. For Datetime, the supported range is '1000-01-01 00:00:00" to "9999-12-31 23:59:59". For Timestamp the range is "1970-01-01 00:00:01" to "2038-01-19 03:14:07".
(b) According to ISO Standard 8601, the date 23 January 2013 is represented as 2013-01-16.
(c) UTC stands for Coordinated Universal Time.
(d) Oracle database can store smallest Date value is 0001-01-01.
(e) Oracle database can only store up to second in a date field.
B(3) Database Integrity Constraints
There are a lot advantages when declaring integrity constraints in the tables rather than implementing it via the programs.
When defining integrity constraints, one only need to have to use SQL statements. There is no need to do programming or coding. SQL statements are more easy to write than programming or coding. Also using SQL statement will eliminate the programming errors. Using integrity constrains will not eliminate the flexibility of ad hoc data access.
Integrity constraints are not defined for application but specially for tables. They are kept in the data dictionary. Although integrity constraints are defined in the tables, data entered by any application must stick to the same integrity constraints defined in the tables.
If a changes is needed to be made to a business rule enforced by an integrity constraints, the administrator only need to change that integrity constraints. Without even changing any coding, the applications are automatically stick to the modified constraint. This save the trouble of having developers to modify application source codes, recompiling, debugging and testing the application all over again if the business rule is enforced in the code of the database application.
Unicode is a universal encoded character set that using a single character set, it allows information from all different language to be stored. Unicode will provide a unique code value for every character regardless of the language, program and platform.
It is very hard and complicated to deal with many different languages in a application or database thus to overcome the limitations of existing character encodings, Unicode is created. Unicode can be used to implement different character encodings. For Unicode was created, there are many different encoding systems for assigning numbers. Even for language like English, no single encoding is enough for all the letters, symbols or punctuation. Too many different encoding systems used will conflict with one another.
In Oracle, NCHAR and NVARCHARS are the datatypes that store Unicode character data.
B(5) Query Optimization
Query (b) is most likely to execute faster than (a) and (b). The long the prefix it is, the smaller range for the scanned index it will be. This help to scan the index faster and takes lesser timing. Query (a) has a prefix of the letter "V" only, (b) has a prefix of "Volg" while (c) has a prefix of "Vol". Thus Query (b) has a longer prefix which make it take lesser time to execute.
For Example :
(a) LIKE 'V%grad'
(b) LIKE 'Volg%grad'
(c) LIKE 'Vol%grad'
(in red = match)
For the table we can see that (b) only got 1 match so it is faster.
B(6) Database statistics
Statistics is automatically created on the index columns when creating an index. While the index is still around, the stats cannot be dropped even if the index is a multi-column one, the density is stored for all columns while the histogram stored only the leading column.
SQL will update the statistics when changes to a column are made. The stats do not update as part of a data modification.
Statistics are like a form of dynamic metadata which helps the query optimizer to make better decision like estimating number of rows each operator in the query plan will operate on. Some row count work better on small rowsets while the others work better on larger row sets. The query optimiser can get a relatively accurate calculation of the cost of each operator by giving it a good estimation.
Without accurate statistics, it can lead to badly performed queries as the query optimiser may calculate the cost of operators wrongly.
B(7) Database Security
Nowadays internet has expand exponentially and there are a lot of hackers trying to steal data from organizations. Thus, those hackers came up with a web attack mechanisms called the SQL Injection. It is an attack which make use of poor coding of web application which give opportunity for the hackers to insert malicious SQL commands to allow them to have access to the targeted Organization's database.
It is very common for web application to allow legit website guests to able to submit and retrieve data to and from a database with internet. Databases are designed to allow websites to retrieve data and deliver and render specific content to visitors, customers, suppliers, employees or even stakeholders. Details like payment information, user credentials and company statistics are normally stored within a database and accessed by legit users through custom web applications.
SQL Injection is a hacking technique which tries to pass a SQL statements through a web application for execution via a backend database. If the web application and database is not protected well enough or is vulnerable, it will be targeted by SQL Injection which hackers has access to all the information in the database. It is as good as giving the database to the hacker as it gives him the ability to execute any commands he/she likes.
SQL Injection is serious and it is normally after the hacker has successfully gained accessed and tempted the database then it is discovered.
B(9)Optimizing by Partitioning
Partitioning is a function to make it possible for a table to be separated into smaller tables. It's intention is to help out in the maintenance of larger tables and to provide faster ways to load and eliminate huge chunks of data from a table. Partitioning helps to improve performance and make maintenance easier for database. Partitioning is useful for building systems that require high availability requirements.
When partitioning a table in a database, the table is divided into many smaller pieces, each piece of the object is called a partition. A partitioned object has several pieces which can be maintained separately. It is easier and flexible for the database administrator to manage the partitioned object. For example, a database administrator is able to compress a partitioned object rather than a entire table. When the database administrator wants to remove data, it is much more faster to drop an entire partition rather than deleting each row individually.
Partitioning helps to improve the performance of a query, depending the condition. Partitioning can be carried out by splitting a large table into many small tables and queries that access to part of the data can run faster as there is lesser data to scan through. Partitioning also can improve maintenance task like backing up a table or rebuilding indexes. Partitioning can be done by physically storing tables on individual disk drives without the need to split the tables. Performance of a query is improved by a method called Partitioning Pruning. It helps to eliminate partitions if it did not contain the data the query needed. For example, there is an ORDERS table storing all the historical record of the orders and the table is partitioned by day. A query that requests orders for a single month will only need to access thirty partitions instead of many more partitions.
Each partitioned objects gain partition independence. It gives a high availability. Let says one a partitioned object is not available, all the other partitions of that table is still available. Application is still able to make use of the partitions and able to execute queries and transactions. Database operations will still able to run smoothly if they do not need the available partition. The database administrator can designate partition to be stored in a separate tablespace which allows the administrator to do recovery and backup operations on each individual partition. So even with system crashed or being hacked, the database still can be recovered. Partitioning can reduce scheduled downtime. With better performance by partitioning, the database administrator can complete maintenance tasks on large database objects in somewhat smaller batch windows.
With Partitioning, it is able to possible to store large chunks of data with the lowest possible cost.
With Oracle Partitioning, it provides three fundamental data distribution methods that manage how the data will go into the different individual partitions. The three methods are the range partitioning, list partitioning and hashing partitioning. When using one of the method, the table can be partitioned into either a Single Partitioning or Composite Partitioning. Other than these few basic partitioning strategies, Oracle also provides extensions to partitioning. There are the Interval Partitioning, REF Partitioning and Virtual column-based Partitioning. In Oracle Database 11g release 2, Oracle also enhanced another facility called the SQL Access Advisor which helps to generate partitioning recommendations.