Database Management Systems

When QUERY_PLAN option is enabled, Sybase IQ writes the generated query plan to the iqmsg file, which is the standard message log file. This file contains diagnostic information including query plans for optimization analysis.

Selectivity estimates the fraction of rows matching a predicate, crucial for cost-based optimization. Usefulness indicates how effectively a search argument filters the result set. Together they guide the optimizer in choosing efficient access paths.

Sybase IQ uses columnar storage with aggressive compression algorithms, significantly reducing storage footprint compared to uncompressed flat files. Data loaded from raw files is compressed and optimized for analytical queries.

Every Sybase IQ database requires three core DBspaces: Catalog store (metadata), IQ store (main data), and IQ Temporary store (query processing). Metadata store is not a standard Sybase IQ DBspace type.

Multiple column-oriented databases exist in the market, including Vertica, Exasol, Amazon Redshift, and Google BigQuery. Sybase IQ was one of the pioneers but is not the only option available.

In Oracle E-Business Suite, a Set of Books is the entity that partitions data into logical units by defining the accounting context including currency, calendar, and chart of accounts. Organizations exist within a Set of Books, and Applications use the Set of Books for data segregation. Option B is correct - Set of Books provides the logical partitioning mechanism. Options A and C are incorrect because they represent different concepts in the hierarchy.

Index Scan (A) reads entire index instead of seeking specific values - indicates missing or non-selective index. Table Scan (B) reads entire table - worst performance indicator. Bookmark Lookup (C) or Key Lookup requires jumping from index to heap - expensive random I/O. Index Seek (D) is the efficient operation - it's NOT a performance problem indicator. These three (A, B, C) appearing in execution plans suggest missing indexes, non-selective predicates, or inappropriate index columns.

Dynamic Management Views (DMVs) provide runtime statistics about query performance, resource utilization, and indexes. DBCC commands like DBCC SHOW_STATISTICS and DBCC FREEPROCCACHE help diagnose and manage performance. Query Plans reveal actual execution strategies and bottlenecks. BCP (Bulk Copy Program) is a data import/export utility, not a performance diagnostics tool. The first three are essential for identifying slow queries and high CPU usage.

In specific model-driven development environments, the Model Shared Pool (MSP) serves as a centralized repository within the model to store and manage different versions of components. This allows developers to share, reuse, and track component versions across the project.

Bitmap indexes are highly efficient for columns with low cardinality, where data is repetitive and contains many duplicate values (such as gender or marital status) across large tables. They use bit arrays to perform fast boolean operations, making them ideal for data warehousing environments.

DDL stands for Data Definition Language, which includes SQL commands that define database structures like CREATE, ALTER, DROP for tables, indexes, views, and schemas. It deals with schema definition rather than data manipulation (DML) or data control (DCL). Option D uses Link incorrectly, and B uses Description instead of Definition.

DBCC CHECKTABLE checks the integrity of a table or indexed view, verifying page linking, structural integrity, and performing internal consistency checks. CHECKIDENT checks identity values, CHECKALLOC verifies allocation structures, and CHECKINTEG isn't a valid DBCC command.

SQL Server 2005 introduced CLR integration, allowing users to create assemblies using .NET code. This was a major new feature that enabled stored procedures, functions, triggers, and types to be written in C#, VB.NET, or other .NET languages. The CREATE ASSEMBLY statement is used for this purpose.

SQL Server functions are called using schema qualification: DatabaseName.SchemaOwner.FunctionName(). The default schema owner is dbo, so DatabaseName.dbo.FunctionName() is correct syntax. The schema qualification is required for UDFs (User-Defined Functions) when calling from outside the function's database.

Transact-SQL reserves certain keywords that have special meaning in the language. datetime is a data type keyword, hierarchyid is a data type for hierarchical data, and status is a reserved keyword. views and value are NOT reserved keywords in T-SQL. Knowing reserved words is important to avoid naming conflicts.